.NET vs SQL Cursor speed

I have read that looping through a ADO.NET data reader is much faster than even the fastest SQL Cursor (the fast_forward cursor).

Why is this so? And if this is so, then why doesn't Microsoft re-write the SQL Cursor logic to match that used by .NET? (I know that currently, one could use CLR integration techniques to achieve this)...

Request a specific answer to the above, not the usual stuff regarding not using cursors and using Set based logic, which is all well known and accepted stuff.!!!

more ▼

asked Oct 19, 2011 at 10:13 PM in Default

avatar image

177 8 11 14

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

My estimation at the reasoning here is that there is no real reason to re-write the T-SQL cursor logic, set based operations are faster than the cursor would be so you would be wasting effort for no benefit. Either that, or the changes required to make the cursor more effective would have implications across the whole of the SQL Server code base that again its more effort than benefit.

more ▼

answered Oct 20, 2011 at 01:30 AM

avatar image

Fatherjack ♦♦
43.8k 79 99 118

(comments are locked)
10|1200 characters needed characters left

Also, the SqlDataReader class allows more or less nothing and offers more or less no functionality. It's simply a much more limited creature then the simplest T-SQL cursor. The SqlDataReader "locks" the database connection it's using - you can't use that connection to do anything else than reading the next row while the SqlDataReader is open. Inside a cursor loop, you can do more or less whatever you want.

more ▼

answered Oct 20, 2011 at 01:52 AM

avatar image

Magnus Ahlkvist
22k 20 41 42

(comments are locked)
10|1200 characters needed characters left

The biggest thing for me is, why do you care? You say you already know that set-based operations are better. Fine, use them. Cursors are there primarily as a limited construct best used for maintenance or very small data sets. They're not meant for large scale data processing. In fact, the cursors in SQL Server that are meant for large scale data processing are working for you all the time and they do work better than the .NET ones and Microsoft is constantly improving how they do what they do. Confused? Lookup the definitions for hash join and loop join. There are your cursors.

more ▼

answered Oct 20, 2011 at 03:40 AM

avatar image

Grant Fritchey ♦♦
137k 20 44 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 19, 2011 at 10:13 PM

Seen: 2003 times

Last Updated: Oct 19, 2011 at 10:13 PM

Copyright 2017 Redgate Software. Privacy Policy