Measuring cursor performance for a massive query

Question

Measuring cursor performance for a massive query

pmscorca

SSCrazy

Points: 2349
More actions
November 2, 2010 at 4:55 pm

#388154

Hi.
I have a strange issue. I have created some massive queries (INSERT INTO ... SELECT ... FROM, UPDATE ... SET ... FROM ..., etc.) without using an object as a cursor. Now, for error management purpose, my customer has asked to me to register into a log which record could generate an error (f.e. conversion error, arithmetic overflow). To solve this request I need to introduce in my sp the try and the catch blocks and in the catch block I need to write the query logic by using a cursor. Before to change the stored procedure, my customer has asked to me how long it takes a query with a cursor respect a query without cursor.
Well, does exist a manner to estimate this time if I know the table dimension, the number rows in the table and the query on the table?
Any ideas to solve this issue, please? Many thanks

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I wouldn't change things to cursors, I would find offending rows with a query instead.

It's not very difficult to find, for instance, non-numeric values stored in a column that has to be transformed into a number.

As for the original question is concerned, I don't think there's a way to predict how long the procedure will take with a cursor without trying.

-- Gianluca Sartori

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

Sound to me like a job for SSIS to clean the data prior to processing

Clear Sky SQL
My Blog[/url]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Despite what others have said, precleaning the data is not necessarily a workable solution for some cases. If you need a cursor to trap and log individual errors and continue processing, then you need a cursor. Be sure to use the RIGHT kind of cursor though. FAST_FORWARD is a good option, although Hugo Kornelis has shown that local static forward_only read_only can be a tad faster. Both assume one record at a time forward movement though.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

TheSQLGuru (11/4/2010)
... although Hugo Kornelis has shown that local static forward_only read_only can be a tad faster.

Do you have any reference to point me to?

Recently a MS consultant asked me why we were using static forward_only read_only cursors instead of fast_forward. We're doing things this way beacause I read something on the topic, but I could not find a link to a blog/article.

-- Gianluca Sartori

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx

Note how Adam Machanic states that CLR cursors are even faster. 😀

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

Thank you!

It's strange, indeed. STATIC cursors have to make a tempdb copy of the data, I would expect this to take some time and lose compared to KEYSET. Go figure.

-- Gianluca Sartori

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

Gianluca Sartori (11/4/2010)
Thank you!
It's strange, indeed. STATIC cursors have to make a tempdb copy of the data, I would expect this to take some time and lose compared to KEYSET. Go figure.

Probably fast because it never leaves memory. Now, if you put a BUNCH of rows (or lots of fat ones) in the cursor and it gets flushed to disk that could be a different story.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service