Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kimi86
Yak Posting Veteran
79 Posts |
 Posted - 2012-07-05 : 10:56:13
|
| I have a question related to cursor performanceI wrote a cursor that loops through a column in a table and then does some logic on it and then inserts values to another table. This is just a made up example though.If I loop through 1000 rows it takes an hour. If a loop through 200 it takes 5 mins... Y is there so much difference. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-07-05 : 11:01:57
|
| The question you should be asking is, "how can I modify this so that it is set based?". Lose the cursor and you can probably do the 1000 in just a few seconds.Post your actual code if you want a solution.Be One with the OptimizerTG |
 |
|
|
Kimi86
Yak Posting Veteran
79 Posts |
Posted - 2012-07-06 : 04:32:15
|
| well i certainly needed a cursor and so i have written one.. Could any one just tell me y is there so much difference in the time taken for traversing through 1000 records (1 hour) and time taken for 200 (5 minites) |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-07-06 : 06:32:29
|
| Not without a LOT more information. You could be blocking or a lot of other things.Also, telling us how you determined that you CERTAINLY needed a cursor would help. Perhaps the cursor could be optimized.How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-06 : 09:36:10
|
quote:
Originally posted by Kimi86
well i certainly needed a cursor and so i have written one.. Could any one just tell me y is there so much difference in the time taken for traversing through 1000 records (1 hour) and time taken for 200 (5 minites)
why do you think you need one?what is it that you're trying to iterate and calculate in loop? If you want us to help you, post some sample data to explain your scenario and we may be able to help you out with a set based alternative------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-07-06 : 13:26:55
|
quote:
Originally posted by Kimi86
well i certainly needed a cursor and so i have written one.. Could any one just tell me y is there so much difference in the time taken for traversing through 1000 records (1 hour) and time taken for 200 (5 minites)
As others have said we can't really help with the limited info you have provided. The reason could be as simple as which set of 200 you are processing. Perhaps the "last" 200 would take much longer than the "first" 200 because of data differences. Perhaps the memory usage for the cursor needs to start writing to disk after a certain amount of iterations. I would like to see what code is executed within the cursor. Are you calling another routine which only takes one entity at a time? Those types of procedural (one at a time) methodologies are just not good for sql server. I can understand if you have an entire system built like that that you may not be willing to take the time necessary to convert it to a set based solution. But if the problem is that you just don't have the expertise we can help.A (very small) percentage of cases really does need to be procedural. In that case you would need to include logging so you can track the duration of each step to see exactly where the time increases. Once you know that you can focus your efforts on just that area.Be One with the OptimizerTG |
|
|
|
|
|
|
Cursor Performace