CODEWITHSUNDEEP
t-sqlsql-server-2008-r2cursor
Mazhar
Mazhar

Reputation: 3837

TSQL CURSOR and SET Based Alternatives

I've read in many places that replacing a CURSOR with a set-based alternative improves performance considerably but I've yet to find an example, tutorial, explanation to what a set-based alternative actually is and how a cursor can be converted into one.

Can anyone provide any links to such please?

Thanks

Upvotes: 1

Views: 1812

Answers (1)

jean
jean

Reputation: 4350

Go for the Relational in RDBMS and you ill see MSSQL, Oracle and others are systems optimized to work with sets. Cursors works more like imperative procedural languages like C#.

For a small example try to implement a join! You can mimic a join by using a cursor to do a nested loop. Silly example for sure but you get the idea. A join ill be faster than that cursor.

Also note performance is not only about the fatest way. It's about using less resources. That resources are: CPU, Memory, IO, HD, users patience (time). Cursors can consume all that resources.

Sometimes cursors can be optimzed using FAST FORWARD and others tricks. Eventualy a cursor can be a option and even the best tool for the work (they exists for a motive).

The problem with cursors is they are over used by developers with lack of set based experience. Those guys tries to apply that C-like programing style to the relational world with horrific results.

Edit Here a example borrowed from SQL Shack

DECLARE @rowguidVar UNIQUEIDENTIFIER  -- prepare unique ID variable to use in the WHERE statement below

DECLARE test_cursor CURSOR FOR  
SELECT rowguid
FROM   AdventureWorks2012.Sales.SalesOrderDetail
WHERE  ModifiedDate BETWEEN '2008-07-15 00:00:00.000' AND '2008-07-31 00:00:00.000'

OPEN test_cursor  
FETCH NEXT FROM test_cursor INTO @rowguidVar  
--This is the start of the cursor loop.
WHILE @@FETCH_STATUS = 0  
BEGIN  
       SELECT *
          FROM            Sales.SalesOrderDetail
          WHERE    rowguid = @rowguidVar
   FETCH NEXT FROM test_cursor INTO @rowguidVar  
END

CLOSE test_cursor  
DEALLOCATE test_cursor
-- Don't forget these statements which flush the cursor from memory

Is the same of

SELECT  *
FROM    AdventureWorks2012.Sales.SalesOrderDetail
WHERE   ModifiedDate BETWEEN '2008-07-15 00:00:00.000' AND '2008-07-31 00:00:00.000'

Upvotes: 1

Related Questions