THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

Poor men see sharp - more cursor optimization

This blog has moved! You can find this content at the following new location:

https://SQLServerFast.com/blog/hugo/2007/11/poor-men-see-sharp-more-cursor-optimization/

Published Wednesday, November 28, 2007 12:47 AM by Hugo Kornelis

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

a.m. said:

Yes, DataSets are dogs :(... I actually wrote a lightweight replacement for a client a while back that used a SqlDataReader and loaded the data into arrays internally and exposed a small subset of the properties and methods that a real DataSet / DataTable / DataRow does (i.e., the only ones that most people ever use <g>).  Extensive perf testing showed that the custom lightweight version was about 2x as fast to fill, and no slower to retrieve data from, than the Framework's version.  The solution was targeted for .NET 1.1 and I'm not sure if that figure would be valid today, but if you're really really bored you might try rolling your own to see what happens...

By the way, please vote here for MARS in SQLCLR, which would solve this problem:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=312087

November 27, 2007 6:18 PM
 

Remus Rusanu said:

For an extra bit of performance in the SQLCLR cursor you can use the CommandBehavior.SequentialAccess in ExecuteReader (). This gives also gives much nicer stream semantics to large data columns.

November 28, 2007 12:59 AM
 

R. S. Reitz said:

But how does the CLR read-only-once option compare to the set-based solution?

November 28, 2007 9:27 AM
 

Hugo Kornelis said:

Adam: You are severely overestimating my abilities in .Net coding if you think that I am able to roll my own DataSet replacement. However, if you send me the code (or the bits), I'll gladly give my test code another run or two.

I voted 4 for the MARS suggestion. I would have voted 5 if I had the idea that SQLCLR is or should be widely used, but I think it'll always remain a useful option to have as the ideal solution for a small percentage of problems.

Remus: Good point. I tested with and without this option, and saw no statistically relevant difference (only 2 ms difference after 5 runs for each version).

R.S.Reitz: The set-based solution can start late, pause halfway for a cup of tea, and will still arrive early enough to have dinner ready long before any of the iterative versions arrive. It takes a mere 188 ms.

November 28, 2007 2:21 PM
 

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' said:

Some problems can only be solved by brute-forcing every possible combination. The problem with such an

November 30, 2007 4:58 PM
 

Venkatesan Prabu said:

Nice post mate.

Cheers,

Venkatesan Prabu .J

http://venkattechnicalblog.blogspot.com/

November 25, 2009 12:00 PM

Leave a Comment

(required) 
(required) 
Submit

About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.
Privacy Statement