Cursor and SetBased Thearoy

  • Hi,

     

    I want to remove cursor and need to apply set based thearoy logic.

    How to apply Set Based thearoy logic ? It would be highly appreciable if anybody give me a real time example and sqlcode.

     

    Regards

    Karthik

     

    karthik

  • You need to provide some example of what you are trying to do. That's too open ended a question.

  • See what I posted here... http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=395869

    Then, as Steve suggested, post the code you want to convert (provided it's NOT hundreds of lines long)

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    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.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • My Code is,

    ----------------------------------------------------------------------

    select name into #t1   

    from sysobjects   

    where type ='U' 

    and name not in ('ProductCommitmentWorkflow', 

                     'Tranche', 

                     'CompanyTrans', 

                     'ProductFeatureSelection', 

                     'ProductFeature', 

                     'ProductSubType', 

                     'ProductType' 

                     )    

       

    create unique index id1 on #t1(name)   

       

    declare p1 cursor    

    for select name from #t1   

    for read only   

       

    open p1   

       

    declare @name varchar(40)   

       

    fetch p1 into @name   

    while @@fetch_status = 0   

    begin   

    declare @sql varchar(255)   

       

    select @sql = 'Truncate table '+ @name   

    exec (@sql)   

       

    fetch p1 into @name   

    end   

    close p1   

    deallocate p1   

     

    Regards

    Karthik

     

     

    karthik

  • Karthik,

    Now THAT's a horse of a different color!   The example you posted is for "Flow Control" and there's not much you can do in the form "Set Based" processing here. 

    Still, you need to know that a Cursor has a lot more overhead than a temp table and a While Loop.  We can avoid the Cursor by adding a column to the temp table and changing the way you retrieve each row for the flow control that controls the Truncates... thusly...

    --===== Create the local variables

    DECLARE @Counter  INT           --Just a loop counter

    DECLARE @MyCount  INT           --Remembers the value of @@ROWCOUNT

    DECLARE @SQL      VARCHAR(8000) --Contains the dynamic SQL to truncate the tables

    --===== Create a numbered list of tables to truncate

     SELECT IDENTITY(INT,1,1) AS TableNum, --Creates a number column to support RBAR access

            NAME

       INTO #TruncateTableList   

       FROM dbo.SysObjects

      WHERE NAME NOT IN ('ProductCommitmentWorkflow', 

                         'Tranche', 

                         'CompanyTrans', 

                         'ProductFeatureSelection', 

                         'ProductFeature', 

                         'ProductSubType', 

                         'ProductType' 

                         )

        AND OBJECTPROPERTY(ID,'IsTable')     = 1 --Is a table

        AND OBJECTPROPERTY(ID,'IsMSShipped') = 0 --Protects dtProperties and other system tables

                                                 --that have a TYPE of "U".

    --===== Capture how many rows we put into the temp table

        SET @MyCount = @@ROWCOUNT

    --===== Add a primary key just because every table deserves one

         -- and it will also create an index on the TableNum column

         -- which will come in handy later.   

      ALTER TABLE #TruncateTableList

        ADD PRIMARY KEY CLUSTERED (TableNum)

       

    --===== Do the flow control to truncate the captured table names

        SET @Counter = 1

      WHILE @Counter <= @MyCount

      BEGIN

             SELECT @SQL = 'Truncate table ' + Name

               FROM #TruncateTableList

              WHERE TableNum = @Counter

               EXEC (@SQL)

                SET @Counter = @Counter + 1 --Ya just gotta remember to do this

        END

    Now, in this case, there's not much of a speed advantage between using the cursor and the way I did it.  Either way is fine except that the cursor does use some extra resources.  BUT, there are a couple of things I added to your code...

    1. I changed the name of your temp table to make the code a bit more self documenting.  #t1 could mean anything.  #TruncateTableList is self documenting... you KNOW what it contains just by reading the name.
    2. I changed the criteria for which tables to find.  Most folks don't know it, but there is a "System" table that is labelled as a "User" table called dtProperties.  The method I used (using OBJECTPROPERTY) will prevent that eventuallity.  You could also have gotten the table names from the INFORMATION_SCHEMA.Tables view, but even that incorrectly lists the dtProperties tables as a "Base Table".

    Just as another performance tip, you could also write the WHILE Loop like this (notice where the counter increment code is)...

    --===== Do the flow control to truncate the captured table names

        SET @Counter = 1

      WHILE @Counter <= @MyCount

      BEGIN

             SELECT @SQL = 'TRUNCATE TABLE ' + Name,

                    @Counter = @Counter + 1 --Ya just gotta remember to do this

               FROM #TruncateTableList

              WHERE TableNum = @Counter

               EXEC (@SQL)

        END

    p.s. Don't let anyone talk you into using the undocumented "MSForEachTable" 'cause, guess what?... it contains a Cursor!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    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.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi Jeff,

    Thanks !Really i got some idea to use while loop instead of Cursor.

    I have two questions.

    1) What is the difference between Cursor and While Loop ? as you mentioned 'cursor does use some extra resources',can you expalin what kind of extra resources will be used by cursor ?

    2)As you mentioned 'dtproperties' will come under 'dbo'. Can you explain me the purpose of 'dtproperties' ? will it be created automatically during database creation ?

    Please clarify my doubts.

     

    Regards

    Karthik

     

     

    karthik

  • Sure, Karthik... let's answer question #2 first...

    'dtProperties' is a special table used by things like the "Visual Database Design Tools".  It even has to be modified if you want to be able to use earlier versions of the Tools.  Basically, it's a system table that can be altered by the user which is why it's actually considered to be a user table.  And, I don't believe it can be deleted without causing drastic problems in the database.  Every database has one of these tables.

    Back to question #1...

    A While Loop, although a bit slow (sometimes, quite a bit) when compared to Set Based code, doesn't really use much in the form of resources.  A Cursor, on the other hand, must store the "result set" from the cursor declaration somewhere... and guess where that place is???  Sure, TempDB where all temporary objects are created... here's from Books Online about cursors...


    Remarks

    If the cursor is declared with the INSENSITIVE or STATIC option, OPEN creates a temporary table to hold the result set. OPEN fails if the size of any row in the result set exceeds the maximum row size for Microsoft® SQL Server™ tables. If the cursor is declared with the KEYSET option, OPEN creates a temporary table to hold the keyset. The temporary tables are stored in tempdb.

    Static Cursors

    The complete result set of a static cursor is built in tempdb when the cursor is opened. A static cursor always displays the result set as it was when the cursor was opened.


    So, in your original code, you created a temp table and the cursor could make another one on top of that.  And, if you don't declare it correctly, it'll open yet another temp table.  Those temp tables are some of the resources I'm talking about.

    If the cursor is updateable, it may not open a temp table... it'll just hold locks on the source table(s) until you deallocate and close the cursor.

    There have been some pretty serious wars about cursors on this forum.  For the task you want to accomplish, a cursor would probably be fine although opening a cursor on a temp table can have the effect of opening two temp tables... that wastes memory because the tables in TempDB will first try to form in memory and only spill over to the disk if they get too big for memory.  Memory is a precious resource that you don't want to waste.

    As the note about Static Cursors says, there's really not much difference between opening a temp table and using a static cursor until you do some processing.  Not much of a chance to do high speed set based processing with something inherently row based like a cursor... but you can do a lot with a temp table... if you even need one.  And THAT's the key... most of the time you don't need a temp table... why have the system create one when you create a cursor?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    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.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • While not exactly set based, there is no cursor, temp table, or loop required:

    DECLARE @SQL varchar(8000)

    SELECT @SQL = ISNULL(@SQL,'')+'TRUNCATE TABLE [' + name + ']; '

    FROM sysobjects   

    WHERE type ='U' 

        AND name NOT IN ('ProductCommitmentWorkflow', 

                     'Tranche', 

                     'CompanyTrans', 

                     'ProductFeatureSelection', 

                     'ProductFeature', 

                     'ProductSubType', 

                     'ProductType' 

                     ) 

    ORDER BY name

    --PRINT @SQL

    IF LEN(@SQL) > 0

        EXEC (@SQL)

    Andy

  • Very nicely done, David, and a lot closer to set based!  And you even remembered the brackets for "odd" table names that I forgot   Same method is used to create "CSVs" and the like.

    The only reason why I didn't show such a method is because you can run out of space in the varchar if enough tables are present.  If the average length of a table name is 12 characters which would make the average length of each command 31 characters (including the necessary trailing space), you'd run out of space in VARCHAR(8000) at about 258 tables.  Admittedly, that's a fair number of tables...

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    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.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks a lot David and Jeff !

    Really i gathered some knowledge about Cursor and 'SetBased' Thearoy from your posts.

     

    Regards

    Karthik

     

    karthik

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply