x

diff between cursor based approch and set based..?

Hi All,

I am a DBA i knew bit of developmet, can i know the difference betwee set based and cursor basedapproch, i heared in this forum that, fallowing set based is better than cursor based for good performace..? What is the internal mechanisam for both processes...

Thanks In advance...
more ▼

asked Feb 27, 2011 at 10:06 PM in Default

Manikreddy gravatar image

Manikreddy
414 24 26 28

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

Think of it this way. If your wife wants you to fold the washing that's come out of the tumble dryer - she might well say 'can you please fold the washing?'. That's a set based approach - it's an operation over a collection of items.

The cursor based approach would be the equivalent of your wife asking you to fold a t-shirt, then when you had come back with that asking you to fold a pair of trousers, then when you had come back with that, asking if you could fold a jumper. No doubt, that would really hack you off. SQL Server doesn't fare much better, and really prefers to be asked to do things the set based way.
more ▼

answered Feb 28, 2011 at 01:53 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

nice analogy. also says a lot about your home life ;)
Feb 28, 2011 at 02:15 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

Think of it this way. If you are cutting wood so you have logs for your fire over for winter - You might well say I'm going to cut some wood. You would get in your pick-up with your saw and other tools. Cut down a tree, cut it into logs, load the logs onto the truck and bring it all back top stack on your log pile. That's a set based approach - it's an operation over a collection of items.

The cursor based approach would be the equivalent of going to the forest, cutting down a tree, cutting one log off it and bringing it home to stack on the wood pile, then returning to the forest to get another log, bringing it back to the log pile and so on.

No doubt, that would really hack you off, it would cost a lot of resources - fuel, time, traffic on the road etc. SQL Server doesn't fare much better, and really prefers to be asked to do things the set based way.

[absolute plagiarism of Matt's answer - just wanted a LumberJack version rather than doing the washing!!]
more ▼

answered Feb 28, 2011 at 02:14 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

I'm a lumberjack and I'm ok!! :)
Feb 28, 2011 at 02:28 AM WilliamD
Hahahah - yeah, you're a lumberjack, and I'm a pansy. :)
Feb 28, 2011 at 03:05 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

[Jeff Moden][1] (SSC contributor and all round SQL genius) has the following text in his signature over on the SSC site

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.

I've always liked that explanation of set-based vs looping

[1]: http://ask.sqlservercentral.com/users/281/jeff-moden/
more ▼

answered Feb 28, 2011 at 02:31 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.8k 47 49 76

(comments are locked)
10|1200 characters needed characters left

A great way to better try and understand the difference is through one explanation I heard a while back (can't remember who said/wrote it).

When working in T-SQL, try to tell the system what you want to do with the data, not how you want it done.

Most programming is the other way around, you tell the computer what it should do with an eye on the outcome almost being a secondary thought. With T-SQL / Set Based thinking, you will often win by constructing commands that explain what result you want - the query optimiser will then find a good way of arriving at that result.

A cursor is a programming construct that prevents the query optimiser from working with the entire data-set you are wanting to process. You queue up the data and manipulate each row, one at a time. The query optimiser then "thinks" that you are working with one single data row and will decide how to best satisfy your single row request.

An easy way to demonstrate the difference would be to imagine a table with 100 rows and wanting to return the rows to the client:

/* Test data creation */
IF EXISTS ( SELECT  *
            FROM    sys.tables T
            WHERE   name = 'CursorTest'
                    AND type = 'U' ) 
    BEGIN
        DROP TABLE dbo.CursorTest
    END ;
CREATE TABLE dbo.CursorTest
    (Col1 int,
     Col2 varchar(100)) ;
INSERT  INTO dbo.CursorTest
        (Col1,
         Col2)
        SELECT TOP 100
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
                'Row ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT   NULL)) AS varchar(100))
        FROM    sys.columns C
        CROSS JOIN sys.columns

Look at a cursor solution that gets the rows and returns them to the client:

/* Cursor Solution */

DECLARE @Col1 int,
    @Col2 varchar(100)

DECLARE @Results AS TABLE
    (Col1 int,
     Col2 varchar(100))

DECLARE Curs CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT  Col1,
        Col2
FROM    dbo.CursorTest CT

OPEN Curs

FETCH NEXT FROM Curs INTO @Col1, @Col2

WHILE @@FETCH_STATUS = 0 
    BEGIN
        INSERT  INTO @Results
                SELECT  @Col1,
                        @Col2

        FETCH NEXT FROM Curs INTO @Col1, @Col2

    END

CLOSE Curs
DEALLOCATE Curs

SELECT  *
FROM    @Results

Now look at the set based approach:

SELECT Col1,
       Col2
FROM dbo.CursorTest

This is an extremely simplified example, but shows that both will achieve the answer, but the cursor spends most of the time telling the system what to do instead of what results we want.

There are really very few reasons for using cursors nowadays, the programming constructs that are offered from SQL 2005 onwards reduced the need even further.

If you have a cursor based solution and want help removing it, let us know on here and we will gladly help.
more ▼

answered Feb 28, 2011 at 01:30 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

Thanks William, sure i will let you know if i need help on changing cursors to set based.
Apr 08, 2011 at 05:16 AM Manikreddy
(comments are locked)
10|1200 characters needed characters left

On Cursor based approach otherwise procedure approach you are manipulating data set on a row-by-row basis otherwise called looping instead of Set.This will hurts the performance if you are dealing with large number of rows.

On set-based approach you are dealing with one or more set of records and the data manipulation is done using SQL Join Operators like OUTER, INNER JOINS, sub-queries etc. Most of the time Set-based approach outperforms Cursor based or procedural ways because, SQL Server engine can make use of indexes and statistics defined on underlying tables of the sets. Refer [this][1] link for more details

[1]: http://www.codeproject.com/KB/database/SetAndProceduralSQL.aspx
more ▼

answered Feb 27, 2011 at 10:43 PM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x59

asked: Feb 27, 2011 at 10:06 PM

Seen: 4299 times

Last Updated: Feb 27, 2011 at 10:06 PM