Professional Web Applications Themes

Is there any way to loop a temp table or table variable without cursor? - Microsoft SQL / MS SQL Server

Hi! Is there anyway to loop through the records on a temp table or a table variable without using a cursor? Because of performance and memory issue, one of the requirement for my current project is that we are not allow to use cursor. I am trying to archive the following task: For each rows within TEMPTABLE (OR TABLEVARIABLE) EXEC STOREPRCOEDURE(row.id) Loop Thanks in advance! Lawrence Yuen...

  1. #1

    Default Is there any way to loop a temp table or table variable without cursor?

    Hi!

    Is there anyway to loop through the records on a temp table or a table
    variable without using a cursor? Because of performance and memory
    issue, one of the requirement for my current project is that we are
    not allow to use cursor. I am trying to archive the following task:


    For each rows within TEMPTABLE (OR TABLEVARIABLE)

    EXEC STOREPRCOEDURE(row.id)

    Loop

    Thanks in advance!
    Lawrence Yuen
    Lawrence Guest

  2. #2

    Default Re: Is there any way to loop a temp table or table variable without cursor?

    You can't execute a stored procedure 'for each row' without a cursor.
    However, depending on the procedure details, you may be able to join to
    the temp table within the stored procedure in order to perform set
    processing.

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "Lawrence Yuen" <com.au> wrote in message
    news:google.com... 


    Dan Guest

  3. #3

    Default Re: Is there any way to loop a temp table or table variable without cursor?

    You add an identity column to the table and loop through it.

    e.g.
    declare tb table(id int identity,title varchar(80))
    insert tb select title from pubs..titles

    declare i int, txt varchar(255)
    set i=1
    while 1=1
    begin
    select txt='i: '+cast(i as varchar)+' title: '+title from tb where id=i
    if rowcount=0 break
    print(txt)
    set i=i+1
    end


    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net



    "Lawrence Yuen" <com.au> wrote in message
    news:google.com... 


    oj Guest

  4. #4

    Default Re: Is there any way to loop a temp table or table variable withoutcursor?

    Lawrence,

    If this is a one-shot deal and not part of production
    code, you might consider the undoented procedure
    xp_execresultset. Here's an example of its use:

    use tempdb
    go

    create proc p (
    i int
    ) as
    print i
    go

    declare sql nvarchar(4000)
    set sql = '
    select ''exec p '' + rtrim(OrderID)
    from Northwind..Orders
    '
    exec master..xp_execresultset sql, N'tempdb'
    go

    drop proc p

    -- Steve Kass
    -- Drew University
    -- Ref: B0549F05-9856-4800-B1A3-F02D6A763857

    Lawrence Yuen wrote:
     

    Steve Guest

  5. #5

    Default Re: Is there any way to loop a temp table or table variable without cursor?

    Jacco, you are correct that a pseudo-cursor can accomplish the task
    instead of a real cursor and I should have mentioned this in my
    response. However, as you noted, this can still result in sub-optimal
    performance as illustrated by the script below.

    The only way to determine the best approach is for Lawrence to
    experiment with the various techniques suggested. I believe that a true
    set-based solution will probably yield the best performance and address
    concurrency issues as well.

    CREATE PROCEDURE MyProcedure
    CustomerID nchar(10)
    AS
    -- we'll do nothing for this performance test
    GO

    DBCC FREEPROCCACHE
    CHECKPOINT
    DBCC DROPCLEANBUFFERS
    GO

    DECLARE Message varchar(255)
    SET Message = 'Pseudo-cursor started: ' +
    CONVERT(varchar(23), CURRENT_TIMESTAMP, 121)
    RAISERROR (Message, 0, 1) WITH NOWAIT
    DECLARE CustomerID nchar(5)
    SET CustomerID = ''
    WHILE 1 =1
    BEGIN
    SET CustomerID = (
    SELECT TOP 1 CustomerID FROM Customers
    WHERE CustomerID > CustomerID
    ORDER BY CustomerID)
    IF CustomerID IS NULL BREAK
    EXEC MyProcedure CustomerID
    END
    SET Message = 'Pseudo-cursor end: ' +
    CONVERT(varchar(23), CURRENT_TIMESTAMP, 121)
    RAISERROR (Message, 0, 1) WITH NOWAIT
    GO

    DBCC FREEPROCCACHE
    CHECKPOINT
    DBCC DROPCLEANBUFFERS
    GO

    DECLARE Message varchar(255)
    SET Message = 'Real cursor started: ' +
    CONVERT(varchar(23), CURRENT_TIMESTAMP, 121)
    RAISERROR (Message, 0, 1) WITH NOWAIT
    DECLARE CustomerID nchar(5)
    DECLARE Customers CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT CustomerID
    FROM Customers
    WHERE CustomerID > CustomerID
    ORDER BY CustomerID
    OPEN Customers
    WHILE 1 =1
    BEGIN
    FETCH NEXT FROM Customers INTO CustomerID
    IF CustomerID IS NULL BREAK
    EXEC MyProcedure CustomerID
    END
    CLOSE Customers
    DEALLOCATE Customers
    SET Message = 'Real cursor end: ' +
    CONVERT(varchar(23), CURRENT_TIMESTAMP, 121)
    RAISERROR (Message, 0, 1) WITH NOWAIT
    GO

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP


    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... 
    cursor 
    LOCAL 
    resultset 
    or in [/ref]
    cursor. [/ref]
    to [/ref][/ref]
    table [/ref][/ref]
    are [/ref][/ref]
    task: 
    > >
    > >[/ref]
    >
    >[/ref]


    Dan Guest

  6. #6

    Default Re: Is there any way to loop a temp table or table variable without cursor?

    Hi Dan,

    There was a mistake in your code, you declared the cursor accidentally to be
    only for customers where the customerID > NULL, and as we know that are not
    many ;-) No wonder the real cursor performed better, as it had no rows to
    process.

    I changed the code to correct this and also used the Orders table, because
    it is larger, and in my experiments the pseudo-cursor has a minute advantage
    over the real cursor, although if the ORDER BY clause is removed from the
    SELECT for the cursor, the real cursor has a slight advantage (see the code
    at end of the post). As always, your mileage may vary, but in general I
    think a pseudo and a real cursor will give similar performance, which
    supports your argument that a set based solution is a better option.

    CREATE PROCEDURE MyProcedure
    OrderID nchar(10)
    AS
    -- we'll do nothing for this performance test
    GO

    DBCC FREEPROCCACHE
    CHECKPOINT
    DBCC DROPCLEANBUFFERS
    GO

    DECLARE Message varchar(255)
    SET Message = 'Pseudo-cursor started: ' +
    CONVERT(varchar(23), CURRENT_TIMESTAMP, 121)
    RAISERROR (Message, 0, 1) WITH NOWAIT
    DECLARE OrderID nchar(5)
    DECLARE rows_processed INT
    SET rows_processed = 0
    SET OrderID = ''
    WHILE 1 =1
    BEGIN
    SET OrderID = (
    SELECT TOP 1 OrderID FROM Orders
    WHERE OrderID > OrderID
    ORDER BY OrderID)
    IF OrderID IS NULL BREAK
    EXEC MyProcedure OrderID
    SET rows_processed = rows_processed +1
    END
    SET Message = 'Pseudo-cursor end: ' +
    CONVERT(varchar(23), CURRENT_TIMESTAMP, 121)
    + ' rows processed: ' + CONVERT(VARCHAR(10), rows_processed)
    RAISERROR (Message, 0, 1) WITH NOWAIT
    GO

    DBCC FREEPROCCACHE
    CHECKPOINT
    DBCC DROPCLEANBUFFERS
    GO

    DECLARE Message varchar(255)
    SET Message = 'Real cursor started: ' +
    CONVERT(varchar(23), CURRENT_TIMESTAMP, 121)
    RAISERROR (Message, 0, 1) WITH NOWAIT
    DECLARE OrderID nchar(5)
    DECLARE rows_processed INT
    SET rows_processed = 0
    DECLARE Orders CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT OrderID
    FROM Orders
    --WHERE OrderID > OrderID
    --ORDER BY OrderID
    OPEN Orders
    WHILE 1 =1
    BEGIN
    FETCH NEXT FROM Orders INTO OrderID
    IF FETCH_STATUS <> 0 BREAK
    EXEC MyProcedure OrderID
    SET rows_processed = rows_processed +1
    END
    CLOSE Orders
    DEALLOCATE Orders
    SET Message = 'Real cursor end: ' +
    CONVERT(varchar(23), CURRENT_TIMESTAMP, 121)
    + ' rows processed: ' + CONVERT(VARCHAR(10), rows_processed)
    RAISERROR (Message, 0, 1) WITH NOWAIT
    GO

    DROP PROCEDURE MyProcedure


    "Dan Guzman" <net> wrote in message
    news:phx.gbl... 
    > cursor 
    > LOCAL 
    > resultset 
    > or in [/ref]
    > cursor. [/ref]
    > to [/ref]
    > table [/ref]
    > are [/ref]
    > task: 
    > >
    > >[/ref]
    >
    >[/ref]


    Jacco Guest

  7. #7

    Default Re: Is there any way to loop a temp table or table variable without cursor?

    Thanks for catching the bug, Jacco. And I didn't think anyone actually
    ran the scripts I post here :-)

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP


    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... 
    to be 
    are not 
    to 
    because 
    advantage 
    the 
    code 
    I [/ref]
    sub-optimal [/ref]
    true [/ref]
    address [/ref][/ref]
    a [/ref][/ref]

    > > resultset [/ref][/ref]
    keys [/ref][/ref]
    database: 
    > > cursor. [/ref][/ref]
    join [/ref][/ref]
    set [/ref][/ref]
    a [/ref][/ref]
    memory [/ref][/ref]
    we 
    > > task: 
    > >
    > >[/ref]
    >
    >[/ref]


    Dan Guest

  8. #8

    Default Re: Is there any way to loop a temp table or table variable without cursor?

    >> Is there anyway to loop through the records [sic] on a temp table or
    a table variable without using a cursor? <<

    Records are not rows; failure to understand this is probably why someone
    is still writing record-at-a-time code. That is not the right question,
    so the only answers you will get are proprietary kludges. Most of the
    time (99.9%), you can re-write the code and make it set-oriented.

    The only exception that comes to mind are NP-complete optimization
    problems for which one near-optimial answer will do. You do not run
    into many of those outisde of a computer science department ...

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. Temp table help...
    By trtpravin@yahoo.com in forum Informix
    Replies: 1
    Last Post: September 26th, 10:26 PM
  2. SQL Server 2000 SP3: Why is Table variable slower than temp tables?
    By FL in forum Microsoft SQL / MS SQL Server
    Replies: 10
    Last Post: July 14th, 02:07 PM
  3. insert into temp table
    By Dhiren_Dave@ril.com in forum Informix
    Replies: 0
    Last Post: June 26th, 05:30 AM
  4. SQL - getting rowid into a temp table [368]
    By ART KAGEL, BLOOMBERG/ 65E 55TH in forum Informix
    Replies: 0
    Last Post: June 24th, 07:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139