This post originated from an RSS feed registered with .NET Buzz
by Anand M.
Original Post: TSQL Cursors and CLR Stored Proc
Feed Title: .NET From India
Feed URL: http://www.dotnetindia.com/index.rss
Feed Description: Your daily dose of .NET From India
I remember reading an article on how CLR Stored Procedures are faster than TSQL server side cursors to loop thru records in the server (I seem to have lost the link to that article).
I was working on some performance tuning on some stored proc which used server side cursors. So I thought maybe moving them to CLR proc may improve performance. So I did a small test with a dummy cursor, incrementing a counter inside the loop. For a table with 50K records, the TSQL proc took around 3 secs, while the CLR proc took <1 sec.
The procedure I was tuning, really loops thru the table takes certain columns, does some computations and checking (some complex logic here, which is why I had to use cursors in the first place) and then updates another table with the final value. So I converted the TSQL proc to a CLR stored proc and checked the performance. The CLR proc was taking longer than the TSQL proc. My guess is that the TSQL proc, though it has a slower cursor, seems to have faster execution of the Update statements. Since there were 50K updates to be done, I guess the performance gains due to the faster cursor was overshadowed by the slower updates taking place.
So, there is no easy rule on performance when comparing TSQL and CLR procs. Do a performance test before you choose which to use.
Previous Topic
