Hello,
I moved a sql 2000 DB to SQL 2005 and it seems to be very slow.
The same DB on a slower machine running 2000 runs mutch faster.
For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102
seconds, 20 times slower.
I reindexed and that did not help.
Any Ideas?
Why the same DB with same indexes behaving that way, what Am I missing'
This is the release version of sql2005, Stardard edition, RTM...
Thanks
SAAre you accessing the database from a .Net 1.1 application using the standar
d
SqlConnection / related classes? I found that my .NET 1.1 apps would not
connect to a SQL Server 2005 instance using a shared memory connection.
Recompiling the same code with the .NET 2.0 framework resolved the issue -
the application again connected using shared memory and was sigificantly
faster. I don't know how to go about determining what mode (tcp / names
pipes / shared memory) a given connection is using, but I'm sure a quick
search will answer that.
Ross
"MSDN" wrote:
> Hello,
> I moved a sql 2000 DB to SQL 2005 and it seems to be very slow.
> The same DB on a slower machine running 2000 runs mutch faster.
> For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102
> seconds, 20 times slower.
> I reindexed and that did not help.
> Any Ideas?
> Why the same DB with same indexes behaving that way, what Am I missing'
'
> This is the release version of sql2005, Stardard edition, RTM...
>
> Thanks
> SA
>
>|||Have you run UPDATE TATISTICS with FULLSCAN option?
"MSDN" <sql_agentman@.hotmail.com> wrote in message
news:OD02m01NGHA.2668@.tk2msftngp13.phx.gbl...
> Hello,
> I moved a sql 2000 DB to SQL 2005 and it seems to be very slow.
> The same DB on a slower machine running 2000 runs mutch faster.
> For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102
> seconds, 20 times slower.
> I reindexed and that did not help.
> Any Ideas?
> Why the same DB with same indexes behaving that way, what Am I
> missing'
> This is the release version of sql2005, Stardard edition, RTM...
>
> Thanks
> SA
>|||Sorry
UPDATE STATISTICS
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OMQ9gf3NGHA.3864@.TK2MSFTNGP10.phx.gbl...
> Have you run UPDATE TATISTICS with FULLSCAN option?
>
>
> "MSDN" <sql_agentman@.hotmail.com> wrote in message
> news:OD02m01NGHA.2668@.tk2msftngp13.phx.gbl...
>|||MSDN (sql_agentman@.hotmail.com) writes:
> I moved a sql 2000 DB to SQL 2005 and it seems to be very slow.
> The same DB on a slower machine running 2000 runs mutch faster.
> For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102
> seconds, 20 times slower.
> I reindexed and that did not help.
> Any Ideas?
> Why the same DB with same indexes behaving that way, what Am I
> missing'
As Uri pointed you must run UPDATE STATISTICS WITH FULLSCAN on all your
tables. The statistics from SQL 2000 are invalidated when you upgrade.
There may be more to it than that, but start there.
If you need further assistence, please be more specific of what is slow.
Is it certain queries, or is it slower overall? If you run queries from
Query Analyzer, is there still any differences (to rule out connection
issues as suggested in Ross's post). If you run from the local machine
(to exclude network issues)?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Perhaps it's not the database, but configuration of the SQL Server
installation. Also, if this database is running on a new server box, it may
be related hardware or OS configuration.
The following was copied from the MSDN article titled: Checklist: SQL Server
Performance
Use default server configuration settings for most applications.
Locate logs and the tempdb database on separate devices from the data.
Provide separate devices for heavily accessed tables and indexes.
Use the correct RAID configuration.
Use multiple disk controllers.
Pre-grow databases and logs to avoid automatic growth and fragmentation
performance impact.
Maximize available memory.
Manage index fragmentation.
Keep database administrator tasks in mind.
http://msdn.microsoft.com/SQL/2000/...enetcheck08.asp
"MSDN" <sql_agentman@.hotmail.com> wrote in message
news:OD02m01NGHA.2668@.tk2msftngp13.phx.gbl...
> Hello,
> I moved a sql 2000 DB to SQL 2005 and it seems to be very slow.
> The same DB on a slower machine running 2000 runs mutch faster.
> For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102
> seconds, 20 times slower.
> I reindexed and that did not help.
> Any Ideas?
> Why the same DB with same indexes behaving that way, what Am I
> missing'
> This is the release version of sql2005, Stardard edition, RTM...
>
> Thanks
> SA
>sql
Showing posts with label slower. Show all posts
Showing posts with label slower. Show all posts
Friday, March 23, 2012
Friday, March 9, 2012
Move from Cursor to Temp table alot slower
I'm somewhat new to this but
last week I created a sp that goes and gets about 200+ records about 30
times and performs some simple calculations and dumps the data from the
table variable to the screen. This is in query analyzer btw.
To do this I
1. find out how many test samples I have (30 -40)
2. starting at sample 1 iterate thru until the last sample
2a. for each sample I have go get the sample testing result records (200
+ records)
2b. iterate thru those records
2b.1 perform minor calculation and save the sample id, sample
record, and result to a table variable
3. dump out the table variable
Step 2 was done with a cursor where each time thru the samples I'm load the
cursor with the sample result records. Now since every where I reads it says
do not use cursors I figured I'd expand my knowledge and use an alternative.
What I ended up with was a temporary table and every iteration clears out
the table and loads it back up again.
After changing to this method it got significantly slower, like from what
was a blink of an eye to 13 long seconds and I'm not sure why.
Loading the table is done via dynamic sql and the table has an identity
field which I user iterate thru it.
Everything else has remained the same in the procedure so I'm at a loss as
to why it's so much slower.
The cursor was a fast forward read only type so I am guessing that clearing
out and loading the table is the bottleneck ?
Thanks for any thoughts you might have. and yes I'm looking at creating a
single query that can get me all the records at once rather than requerying
30 times but I haven't figured that out yet.
Thanks
When people say "don't use cursors" they really mean "don't process
data a row at a time, write set-based code instead". SQL Server is
optimized for set-at-a-time operations rather than row-at-a-time but
what you have written is really a cursor in disguise.
If you need help with a set-based solution, then describe your problem
fully as explained in this article:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
last week I created a sp that goes and gets about 200+ records about 30
times and performs some simple calculations and dumps the data from the
table variable to the screen. This is in query analyzer btw.
To do this I
1. find out how many test samples I have (30 -40)
2. starting at sample 1 iterate thru until the last sample
2a. for each sample I have go get the sample testing result records (200
+ records)
2b. iterate thru those records
2b.1 perform minor calculation and save the sample id, sample
record, and result to a table variable
3. dump out the table variable
Step 2 was done with a cursor where each time thru the samples I'm load the
cursor with the sample result records. Now since every where I reads it says
do not use cursors I figured I'd expand my knowledge and use an alternative.
What I ended up with was a temporary table and every iteration clears out
the table and loads it back up again.
After changing to this method it got significantly slower, like from what
was a blink of an eye to 13 long seconds and I'm not sure why.
Loading the table is done via dynamic sql and the table has an identity
field which I user iterate thru it.
Everything else has remained the same in the procedure so I'm at a loss as
to why it's so much slower.
The cursor was a fast forward read only type so I am guessing that clearing
out and loading the table is the bottleneck ?
Thanks for any thoughts you might have. and yes I'm looking at creating a
single query that can get me all the records at once rather than requerying
30 times but I haven't figured that out yet.
Thanks
When people say "don't use cursors" they really mean "don't process
data a row at a time, write set-based code instead". SQL Server is
optimized for set-at-a-time operations rather than row-at-a-time but
what you have written is really a cursor in disguise.
If you need help with a set-based solution, then describe your problem
fully as explained in this article:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
Move from Cursor to Temp table alot slower
I'm somewhat new to this but
last week I created a sp that goes and gets about 200+ records about 30
times and performs some simple calculations and dumps the data from the
table variable to the screen. This is in query analyzer btw.
To do this I
1. find out how many test samples I have (30 -40)
2. starting at sample 1 iterate thru until the last sample
2a. for each sample I have go get the sample testing result records (200
+ records)
2b. iterate thru those records
2b.1 perform minor calculation and save the sample id, sample
record, and result to a table variable
3. dump out the table variable
Step 2 was done with a cursor where each time thru the samples I'm load the
cursor with the sample result records. Now since every where I reads it says
do not use cursors I figured I'd expand my knowledge and use an alternative.
What I ended up with was a temporary table and every iteration clears out
the table and loads it back up again.
After changing to this method it got significantly slower, like from what
was a blink of an eye to 13 long seconds and I'm not sure why.
Loading the table is done via dynamic sql and the table has an identity
field which I user iterate thru it.
Everything else has remained the same in the procedure so I'm at a loss as
to why it's so much slower.
The cursor was a fast forward read only type so I am guessing that clearing
out and loading the table is the bottleneck ?
Thanks for any thoughts you might have. and yes I'm looking at creating a
single query that can get me all the records at once rather than requerying
30 times but I haven't figured that out yet.
ThanksWhen people say "don't use cursors" they really mean "don't process
data a row at a time, write set-based code instead". SQL Server is
optimized for set-at-a-time operations rather than row-at-a-time but
what you have written is really a cursor in disguise.
If you need help with a set-based solution, then describe your problem
fully as explained in this article:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--
last week I created a sp that goes and gets about 200+ records about 30
times and performs some simple calculations and dumps the data from the
table variable to the screen. This is in query analyzer btw.
To do this I
1. find out how many test samples I have (30 -40)
2. starting at sample 1 iterate thru until the last sample
2a. for each sample I have go get the sample testing result records (200
+ records)
2b. iterate thru those records
2b.1 perform minor calculation and save the sample id, sample
record, and result to a table variable
3. dump out the table variable
Step 2 was done with a cursor where each time thru the samples I'm load the
cursor with the sample result records. Now since every where I reads it says
do not use cursors I figured I'd expand my knowledge and use an alternative.
What I ended up with was a temporary table and every iteration clears out
the table and loads it back up again.
After changing to this method it got significantly slower, like from what
was a blink of an eye to 13 long seconds and I'm not sure why.
Loading the table is done via dynamic sql and the table has an identity
field which I user iterate thru it.
Everything else has remained the same in the procedure so I'm at a loss as
to why it's so much slower.
The cursor was a fast forward read only type so I am guessing that clearing
out and loading the table is the bottleneck ?
Thanks for any thoughts you might have. and yes I'm looking at creating a
single query that can get me all the records at once rather than requerying
30 times but I haven't figured that out yet.
ThanksWhen people say "don't use cursors" they really mean "don't process
data a row at a time, write set-based code instead". SQL Server is
optimized for set-at-a-time operations rather than row-at-a-time but
what you have written is really a cursor in disguise.
If you need help with a set-based solution, then describe your problem
fully as explained in this article:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--
Move from Cursor to Temp table alot slower
I'm somewhat new to this but
last week I created a sp that goes and gets about 200+ records about 30
times and performs some simple calculations and dumps the data from the
table variable to the screen. This is in query analyzer btw.
To do this I
1. find out how many test samples I have (30 -40)
2. starting at sample 1 iterate thru until the last sample
2a. for each sample I have go get the sample testing result records (200
+ records)
2b. iterate thru those records
2b.1 perform minor calculation and save the sample id, sample
record, and result to a table variable
3. dump out the table variable
Step 2 was done with a cursor where each time thru the samples I'm load the
cursor with the sample result records. Now since every where I reads it says
do not use cursors I figured I'd expand my knowledge and use an alternative.
What I ended up with was a temporary table and every iteration clears out
the table and loads it back up again.
After changing to this method it got significantly slower, like from what
was a blink of an eye to 13 long seconds and I'm not sure why.
Loading the table is done via dynamic sql and the table has an identity
field which I user iterate thru it.
Everything else has remained the same in the procedure so I'm at a loss as
to why it's so much slower.
The cursor was a fast forward read only type so I am guessing that clearing
out and loading the table is the bottleneck ?
Thanks for any thoughts you might have. and yes I'm looking at creating a
single query that can get me all the records at once rather than requerying
30 times but I haven't figured that out yet.
ThanksWhen people say "don't use cursors" they really mean "don't process
data a row at a time, write set-based code instead". SQL Server is
optimized for set-at-a-time operations rather than row-at-a-time but
what you have written is really a cursor in disguise.
If you need help with a set-based solution, then describe your problem
fully as explained in this article:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--
last week I created a sp that goes and gets about 200+ records about 30
times and performs some simple calculations and dumps the data from the
table variable to the screen. This is in query analyzer btw.
To do this I
1. find out how many test samples I have (30 -40)
2. starting at sample 1 iterate thru until the last sample
2a. for each sample I have go get the sample testing result records (200
+ records)
2b. iterate thru those records
2b.1 perform minor calculation and save the sample id, sample
record, and result to a table variable
3. dump out the table variable
Step 2 was done with a cursor where each time thru the samples I'm load the
cursor with the sample result records. Now since every where I reads it says
do not use cursors I figured I'd expand my knowledge and use an alternative.
What I ended up with was a temporary table and every iteration clears out
the table and loads it back up again.
After changing to this method it got significantly slower, like from what
was a blink of an eye to 13 long seconds and I'm not sure why.
Loading the table is done via dynamic sql and the table has an identity
field which I user iterate thru it.
Everything else has remained the same in the procedure so I'm at a loss as
to why it's so much slower.
The cursor was a fast forward read only type so I am guessing that clearing
out and loading the table is the bottleneck ?
Thanks for any thoughts you might have. and yes I'm looking at creating a
single query that can get me all the records at once rather than requerying
30 times but I haven't figured that out yet.
ThanksWhen people say "don't use cursors" they really mean "don't process
data a row at a time, write set-based code instead". SQL Server is
optimized for set-at-a-time operations rather than row-at-a-time but
what you have written is really a cursor in disguise.
If you need help with a set-based solution, then describe your problem
fully as explained in this article:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--
Subscribe to:
Posts (Atom)