SQL Server - SQL Cursor vs ADO.NET

Question

I have to compute a value involving data from several tables. I was wondering if using a stored procedure with cursors would offer a performance advantage compared to reading the data into a dataset (using simple select stored procedures) and then looping through the records? The dataset is not large, it consists in 6 tables, each with about 10 records, mainly GUIDs, several nvarchar(100) fields, a float column, and an nvarchar(max).

Answer 1

Cursors should be faster (unless you're doing something weird in SQL and not in ADO.NET).

That said, I've often found that cursors can be eliminated with a little bit of legwork. What's the procedure you need to do?

Cheers,
Eric

Answer 2

That would probably depend on the dataset you may be retrieving back (the larger the set, the more logical it may be to perform inside SQL Server instead of passing it around), but I tend to think that if you are looking to perform computations, do it in your code and away from your stored procedures. If you need to use cursors to pull the data together, so be it, but using them to do calculations and other non-retrieval functions I think should be shied away from.

Edit: This Answer to another related question will give some pros and cons to cursors vs. looping. This answer would seem to conflict with my previous assertion (read above) about scaling. Seems to suggest that the larger you get, the more you will probably want to move it off to your code instead of in the stored procedure.

Answer 3

Cursors should be faster, but if you have a lot of users running this it will eat up your server resources. Bear in mind you have a more powerful coding language when writing loops in .Net rather than SQL.

There are very few occasions where a cursor cannot be replaced using standard set based SQL. If you are doing this operation on the server you may be able to use a set based operation. Any more details on what you are doing?

If you do decide to use a cursor bear in mind that a FAST_FORWARD read only cursor will give you the best performance, and make sure that you use the deallocate statement to release it. See here for cursor tips

Answer 4

alternative to a cursor

declare @table table (Fields int)
declare @count int
declare @i

insert inot @table (Fields)
select Fields
from Table


select @count = count(*) from @table

while (@i<=@count)
begin


--whatever you need to do
set @i = @i + 1

end