Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I had always disliked cursors because of their slow performance. However, I found I didn't fully understand the different types of cursors and that in certain instances, cursors are a viable solution.</p> <p>When you have a business problem that can only be solved by processing one row at a time, then a cursor is appropriate.</p> <p>So to improve performance with the cursor, change the type of cursor you are using. Something I didn't know was, if you don't specify which type of cursor you are declaring, you get the Dynamic Optimistic type by default, which is the one that is the slowest for performance because it's doing lots of work under the hood. However, by declaring your cursor as a different type, say a static cursor, it has very good performance.</p> <p>See these articles for a fuller explanation:</p> <p><a href="http://bradsruminations.blogspot.com/2010/05/truth-about-cursors-part-1.html" rel="nofollow">The Truth About Cursors: Part I</a></p> <p><a href="http://bradsruminations.blogspot.com/2010/05/truth-about-cursors-part-2.html" rel="nofollow">The Truth About Cursors: Part II</a></p> <p><a href="http://bradsruminations.blogspot.com/2010/05/truth-about-cursors-part-3.html" rel="nofollow">The Truth About Cursors: Part III</a></p> <p>I think the biggest con against cursors is performance, however, not laying out a task in a set based approach would probably rank second. Third would be readability and layout of the tasks as they usually don't have a lot of helpful comments.</p> <p>The best alternative to a cursor I've found is to rework the logic to take a set based approach. </p> <p>SQL Server is optimized to run the set based approach. You write the query to return a result set of data, like a join on tables for example, but the SQL Server execution engine determines which join to use: Merge Join, Nested Loop Join, or Hash Join. SQL Server determines the best possible joining algorithm based upon the participating columns, data volume, indexing structure, and the set of values in the participating columns. So it generally the best approach in performance over the procedural cursor approach.</p> <p>Here is an article on <a href="http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them" rel="nofollow">Cursors and how to avoid them.</a> It also discusses the alternatives to cursors.</p>
    singulars
    1. This table or related slice is empty.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. This table or related slice is empty.
 

Querying!

 
Guidance

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload