Tuesday, February 14, 2012

Cursors in SQL Server

I have read ample technical papers on Cursors. I like to use cursors because it gives you a row by row control in a stored procedure. But most articles I have read advise against the use of cursors (server side).
I typically use read only or Forward Only Cursors. This seems to be the best way to transpose data.
My questions is, is there any guildlines to using cursors? Do you look at the performance of the cursor? If the query runs in 30 seconds and the cursor process is 15 seconds, is that acceptable (based on a million transactions, but filtering 1000-2000 records based on indexed fields.)?
Or is it better to break cursor based steps into multiple queries with insert / update to generate a transposed output? Even if this involves a base query with multiple joins.
Is it better to hammer SQL Server once and then output the results on a row by row, column by column basis, or do multiple queries.
The bottom line is I have been able to provide the required output using cursors, but I just don't want to potentially bog down the system.
Can anyone list specific cases where a cursor was a better method than multiple queries? And particularily with large volume data mining operations.

Thanks
DotNetNow

When you are dealing with large amount of data, cursors become bottle neck to the applications. In an old application I used to handle over 30 million rows in a table. in this kind of situation, cursors fail. In our company we rarely use cursors. Most of the places temp table will solve the purpose of cursors.
Thanks,
Ram

|||Well there is definitely a limitation when you expand the number of records. So I am going to have to rework the flow of the query.
Would like to still hear some comments and thoughts!
DotNetNow|||

ANSI SQL expert Peter Gulutzan who runs test on query engines said when you create a Cursor the query engine says you have asked me to perform task I am not equiped to do so you think I need five loops to perform the task I think I need twenty loops.
Query engines are created to perform SET based tasks a Cursor is Row based it confuses the query engine. Peter Gulutzan and his team gave MySQL stored procs in six months browse his many books at your local book store because before the MySQL job he was RDBMS(relational database management systems) vendor agnostic. Hope this helps.

|||

Don't use cursors. They are a sign of a bad SQL programmer with the mindset of a procedural programmer. SQL is a DECLARTIVE language, and the use of cursors is a throw back to the "old" way of doing things record-by-record. Think about the problem first before sitting down and coding. You'll figure out the right way to do it.

|||Well advise well taken. Cursors are not replace with temp tables and things are well. If they are such a proformance problem, why did Microsoft put in this capability?
Maybe for real complex situations. Just need to slice and dice.
They must have some purpose? But your right if you think about it, you can pretty much do things with temp table(s).
Thanks again for the feedback!
DotNetNow

No comments:

Post a Comment