asked
.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.!!!
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.
+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.
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.
