Hi All,
I am looking if somebody know how can this happen,,,
I have a couple of stored procedures that I run, one SP call the other.
There are cursor, memory table, and recursive query.
When I run this against a local database in my computer it takes arount 15
secs to return the result and it is just fine
considering the table is about 1 + million records. My PC is P4 512 kb RAM
Now, when I create this SP in the server machine and test them against the
database and run it, it takes more than 10 minutes.
Amazing!! considering is the same table, same indexes, but this server has 4
processors and 1 gig RAM.
What can that be? Can it be a MS SQL configuration?
Thanks in advance
RobertoAre statistics up to date on the server?
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>|||What does you mean with statistic are up to date on the server?
"chris" <chris@.noemail.com> wrote in message
news:%23q$6gDlJEHA.3712@.TK2MSFTNGP09.phx.gbl...
> Are statistics up to date on the server?
>
> "Roberto Martinez" <roberto@.ccubetech.com> wrote in message
> news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
15[vbcol=seagreen]
RAM[vbcol=seagreen]
the[vbcol=seagreen]
has[vbcol=seagreen]
> 4
>|||Check the query plan for the queries on both machines to ensure the same
indexes are used both places.
If they differ, try doing
update statistics <tablename>
on each table used in the query, and look at the Query plan, and compare
execution times. again..
Lastly ( perhaps) try setting maxdop to 1 for the query on the multi proc
machine and see if performance improves...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>
Showing posts with label cursor. Show all posts
Showing posts with label cursor. Show all posts
Saturday, February 25, 2012
Different performance in different machines,,,
Hi All,
I am looking if somebody know how can this happen,,,
I have a couple of stored procedures that I run, one SP call the other.
There are cursor, memory table, and recursive query.
When I run this against a local database in my computer it takes arount 15
secs to return the result and it is just fine
considering the table is about 1 + million records. My PC is P4 512 kb RAM
Now, when I create this SP in the server machine and test them against the
database and run it, it takes more than 10 minutes.
Amazing!! considering is the same table, same indexes, but this server has 4
processors and 1 gig RAM.
What can that be? Can it be a MS SQL configuration?
Thanks in advance
Roberto
Are statistics up to date on the server?
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>
|||What does you mean with statistic are up to date on the server?
"chris" <chris@.noemail.com> wrote in message
news:%23q$6gDlJEHA.3712@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Are statistics up to date on the server?
>
> "Roberto Martinez" <roberto@.ccubetech.com> wrote in message
> news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
15[vbcol=seagreen]
RAM[vbcol=seagreen]
the[vbcol=seagreen]
has
> 4
>
|||Check the query plan for the queries on both machines to ensure the same
indexes are used both places.
If they differ, try doing
update statistics <tablename>
on each table used in the query, and look at the Query plan, and compare
execution times. again..
Lastly ( perhaps) try setting maxdop to 1 for the query on the multi proc
machine and see if performance improves...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>
I am looking if somebody know how can this happen,,,
I have a couple of stored procedures that I run, one SP call the other.
There are cursor, memory table, and recursive query.
When I run this against a local database in my computer it takes arount 15
secs to return the result and it is just fine
considering the table is about 1 + million records. My PC is P4 512 kb RAM
Now, when I create this SP in the server machine and test them against the
database and run it, it takes more than 10 minutes.
Amazing!! considering is the same table, same indexes, but this server has 4
processors and 1 gig RAM.
What can that be? Can it be a MS SQL configuration?
Thanks in advance
Roberto
Are statistics up to date on the server?
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>
|||What does you mean with statistic are up to date on the server?
"chris" <chris@.noemail.com> wrote in message
news:%23q$6gDlJEHA.3712@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Are statistics up to date on the server?
>
> "Roberto Martinez" <roberto@.ccubetech.com> wrote in message
> news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
15[vbcol=seagreen]
RAM[vbcol=seagreen]
the[vbcol=seagreen]
has
> 4
>
|||Check the query plan for the queries on both machines to ensure the same
indexes are used both places.
If they differ, try doing
update statistics <tablename>
on each table used in the query, and look at the Query plan, and compare
execution times. again..
Lastly ( perhaps) try setting maxdop to 1 for the query on the multi proc
machine and see if performance improves...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>
Tuesday, February 14, 2012
differences between stored procedure and cursor
differences between stored procedure and cursorStored Procedure :
SP is a set of SQL Statements that resides in server. Advantage of SP is just that it is pre compiled and available in the server. So, whenever this SP is called, its executed instantly since its already been compiled. This makes the faster performance while executing the SP.
We can also make the SP recompile when ever it is called by using
"With Recompile" keyword while creating the SP. So whenever the SP is called it is recompiled and then execute.
Cursors:
Cursors are add on feature of SP , for row by row validations.
For instance : You have a list of employees belong to various department. For calculating the bonus % which varies for department to department, you use cursors to calculate Bonus.
Subscribe to:
Posts (Atom)