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

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
--

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
--