SQL Server Performance

Replace cursors Using table Variable

Discussion in 'Contribute Your SQL Server Scripts' started by EasySQL, Apr 12, 2006.

  1. EasySQL New Member

    I have a sample code that might help you to replace Cursors with table variable.
    This one is good if the number of records are not too many(in thousands).
    Try implementing the following code.

    -- Cursor Simulator
    CREATE PROC CursorSimulator
    AS
    /*Prepare TABLE variable to take resultset*/
    DECLARE @tbl TABLE(
    RowID INT IDENTITY(1, 1),
    CompanyName VARCHAR(100),
    ContactName VARCHAR(50))
    /*Local variables */
    DECLARE @ContName VARCHAR(100),
    @CompName VARCHAR(50),
    @count int, /*create local @@fetch_status*/
    @iRow int /*row pointer (index)*/
    /* create array simulator */
    INSERT @tbl
    SELECT CompanyName, ContactName
    FROM Suppliers
    WHERE ContactName LIKE 'c%'
    /*get array Upper Bound (highest ID number)*/
    SET @count = @@ROWCOUNT
    /*initialize index counter*/
    SET @iRow = 1
    /*establish loop structure*/
    WHILE @iRow <= @count
    BEGIN
    /*get row values*/
    SELECT @ContName = CompanyName, @CompName = ContactName
    FROM @tbl
    WHERE RowID = @iRow
    /*perform operations with single row*/
    PRINT 'My cursor row | ' + @ContName + ' | ' + @CompName
    /*go to next row*/
    SET @iRow = @iRow + 1
    END

    Reference :http://www.sqlservercentral.com/columnists/aGrinberg/thearrayinsqlserver2000.asp
  2. Madhivanan Moderator

    Well. This is moved to relevent forum

    Madhivanan

    Failing to plan is Planning to fail
  3. Adriaan New Member

    This doesn't replace cursors?! You're just copying data from a table into a table variable, and fetching that same data one row at a time.

    What's the point?
  4. EasySQL New Member

    Adriaan
    Well in a scenario like mine does it not replace the cursor .
    I was peforming this using a cursor but i have replaced using the table variable does this help in optimization.
    Pls advice

    DECLARE @count int,@iRow int ,@OLPIN_CONTGROUP int, @OLPIN_CONTCODE CHAR(5),@VENDOR_ID INT
    DECLARE @TableVar table (
    RowID int identity(1,1),
    OLPIN_CONTCODE varchar(25) NOT NULL)

    Insert Into @TableVar (OLPIN_CONTCODE)
    (
    SELECTDISTINCT
    A.DICONT AS OLPIN_CONTCODE
    FROM
    INSP_HDR_INTFC A (nolock)
    WHERE
    NOT EXISTS (SELECT * FROM VENDOR_OLPIN_CONT VOC (nolock)
    WHERE VOC.OLPIN_CONTCODE = A.DICONT)
    AND A.PROCESS_ID = @PROCESS_ID
    )
    SET @count = @@ROWCOUNT
    SET @iRow = 1
    WHILE @iRow <= @count
    Begin
    SELECT @OLPIN_CONTCODE = OLPIN_CONTCODE
    FROM @TableVar
    WHERE RowID = @iRow
    BEGIN
    SELECT@OLPIN_CONTGROUP = INVEND
    FROMVENDOR_OLPIN_MAP (nolock)
    WHEREINVNDNBR = @OLPIN_CONTCODE
    Begin
    INSERT INTO VENDORS
    (
    OLPIN_CONTCODE,
    OLPIN_CONTGROUP
    )
    VALUES
    (
    @OLPIN_CONTCODE,
    ISNULL(@OLPIN_CONTGROUP,0)
    )
    SELECT @VENDOR_ID = @@IDENTITY
    INSERT INTO VENDOR_OLPIN_CONT
    (
    VENDOR_ID,
    OLPIN_CONTCODE
    )
    VALUES
    (
    @VENDOR_ID,
    @OLPIN_CONTCODE
    )
    END
    SET @iRow = @iRow + 1
    END
    END
  5. Adriaan New Member

    I doubt if it makes the slightest bit of difference.

    Can you not write this as some sort of a GROUP BY query? You can INSERT results from any variation of a SELECT query, including GROUP BY.

    This will make the processing set-based, which 9 times out of 10 is faster than using a (fake) cursor.
  6. EasySQL New Member

    Adriaan Here is the Cursor which i have replaced with the above code how can i use the set-based processing.Does set based means doing update and using set column fields.

    DECLARE CUR_VENDOR CURSOR FOR
    SELECTDISTINCT
    A.DICONT AS OLPIN_CONTCODE
    FROM
    INSP_HDR_INTFC A (nolock)
    WHERE
    NOT EXISTS (SELECT * FROM VENDOR_OLPIN_CONT VOC (nolock)
    WHERE VOC.OLPIN_CONTCODE = A.DICONT)
    AND A.PROCESS_ID = @PROCESS_ID

    OPEN CUR_VENDOR

    FETCH NEXT FROM CUR_VENDOR INTO @OLPIN_CONTCODE

    IF @@FETCH_STATUS <> 0
    BEGIN
    SELECT @MSG_TXT = 'VENDORS cursor not executed; continuing'
    END

    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- Code block
    END
  7. Adriaan New Member

    Nobody forces you to program with variables! You can if you must, but you don't have to.

    Also, it looks like you have no idea that there is such a thing as a JOIN in queries - look at the basic SELECT syntax in BOL.

    In a JOIN, you connect data from two tables, and you can include columns from all JOINed tables in the column list. The parameters that you are reading in the first cursor, which you then pass on for opening the second cursor, are mimicking a JOIN.

    If you'll excuse the play on your name - SQL really is much easier than you think!
  8. EasySQL New Member

    Guess youre talking about a inner join in the following and not using a corelated query.
    but a inner join would bring the ones which exists and not the ones which does not exists.
    i am very confused.or maybe i am a ammeture in writing sql and working on making sql easy on me.


    SELECT DISTINCT
    A.DICONT AS OLPIN_CONTCODE
    FROM
    INSP_HDR_INTFC A (nolock)
    WHERE
    NOT EXISTS (SELECT * FROM VENDOR_OLPIN_CONT VOC (nolock)
    WHERE VOC.OLPIN_CONTCODE = A.DICONT)
    AND A.PROCESS_ID = @PROCESS_ID
  9. Adriaan New Member

    You're reading the A.DICONT value here ...

    SELECT DISTINCT
    A.DICONT AS OLPIN_CONTCODE
    FROM
    INSP_HDR_INTFC A (nolock)
    WHERE
    NOT EXISTS (SELECT * FROM VENDOR_OLPIN_CONT VOC (nolock)
    WHERE VOC.OLPIN_CONTCODE = A.DICONT)
    AND A.PROCESS_ID = @PROCESS_ID

    ... and put it into a variable @OLPIN_CONTCODE.

    You then use the variable to read INVEND and put it into a variable @OLPIN_CONTGROUP:

    SELECT @OLPIN_CONTGROUP = INVEND
    FROM VENDOR_OLPIN_MAP (nolock)
    WHERE INVNDNBR = @OLPIN_CONTCODE

    And finally you insert those two values into a table. All this can be done in a single statement:

    INSERT INTO ... (......)
    SELECT DISTINCT A.DICONT, B.INVEND
    FROM INSP_HDR_INTFC A (nolock)
    INNER JOIN VENDOR_OLPIN_MAP B (nolock) ON A.DICONT = B.INVNDNBR
    WHERE NOT EXISTS
    (SELECT * FROM VENDOR_OLPIN_CONT VOC (nolock)
    WHERE VOC.OLPIN_CONTCODE = A.DICONT)
    AND A.PROCESS_ID = @PROCESS_ID

    If you're looping through a cursor to read @PROCESS_ID, and repeat the above, then think again.
  10. EasySQL New Member

    I will try the above query and implement it.
    I think what u are saying would also optimize my code efficiently.
    but i did not get youre last sentence "
    If you're looping through a cursor to read @PROCESS_ID, and repeat the above, then think again"
    means to implement or not to implement?
    Process_id is one but the id's corresponding to process id are multiple the reason for using cursor.
    thanks
  11. Adriaan New Member

    So you are reading @PROCESS_ID from another table? Add that table with a JOIN, just like we did the other two tables, and in case you're filtering that new table, then add the criteria to the WHERE statement.
  12. Madhivanan Moderator

    EasySQL, did you read your article fully?

    Madhivanan

    Failing to plan is Planning to fail
  13. mmarovic Active Member

    Also, don't believe each article on the net. If there is an discussion about the article it is good idea to read it before blindly apply recommendations from there.
  14. mmarovic Active Member

    ... and even more important, test everything yourself first!

Share This Page