x

.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

chaitanyadabholkar gravatar image

chaitanyadabholkar
147 8 8 9

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

3 answers: sort newest
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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

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

+1 to @Fatherjack 's answer.

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

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

(comments are locked)
10|1200 characters needed characters left
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

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x58

asked: Oct 19, 2011 at 10:13 PM

Seen: 1654 times

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