December 11, 2006 at 8:48 am
I'm working on a project where I'm ending up with a queue of transactions to process sitting in a temp table. The transactions each require a stored procedure to run. I've heard that cursors are extremely slow, and there are better ways of handeling things, so I've been looking at some of the other options I can think of or can find reference to, but, Cursors don't seem to be performing anywhere near as bad as I expected. The opposite in fact.
To test, I set up a simple test database, I made a table crstest, and put 3 items in it, a bigint identity field, a bigint item , and a datetime stamp field. I populated first 50,000 records to test, but everything ground to a halt when I did, so I dropped back first to 1k, and then to 6k. with 6k here are the results I got.
With just a straight update to the file (incrementing the item by 1, and updating the datetime with getdate()) 6k records update near instantly.
When I do the same update with a cursor, it runs for 42-43 seconds
when I rewrite the process to use a counter, and a loop to select records from the temp table, and run an update, it jumps to 60-61 seconds.
When I rewrite again this time using a dynamic SQL statement and compiling a long string of updates ("Row-By-Row Processing Without Cursor" posted on 12/07) I end up processing for 49-51 seconds.
Am I missing something obvious here, or are cursors the best option?
I'm really doubting that I'll end up processing much more then 1k transactions with this system any time soon, but I do want to make it as scaleable as possible. So I want to pick the processing method that will be best if I ever do get into the 50k + transaction territory
any ideas?
thanks in advance,
Kevin
December 11, 2006 at 11:30 am
As everybody say... cursors are not goo... but when there is not option you end up using it...
If you are getting the same execution for while loop and cursor you can use based on execution time and resource utilization...
Instead of running SP based on temp table why don't you try to create a new proc using the existing proc and include temp table processing in it...may be you may end up with no cursor.
MohammedU
Microsoft SQL Server MVP
December 11, 2006 at 11:44 am
I try to avoid cursors when I can. I know I can probably rewrite the procedure to do direct inserts/updates, but at the same time I'm a bit hesitant to do so as it ends up making duplicate code. I'm writing a game, and it's going to evolve as time goes on. I don't want to worry about if I had the same action coded somewhere else. unless it becomes absolutely necessary.
I guess my best bet is to just go with the cursor, and do everything I can to limit the number of transactions. If I ever go much about 4k transactions per execution (probably 1 execution per min) then I'll worry about how I can speed things up.
What really suprised me was that the cursor performed better then the other options that I had seen people claiming would speed things up.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply