Use WHILE LOOP Over CURSORS

Posted: August 11, 2010 in SQL Server 2005

Experiencing performance degradation due to cursor processing? Use a WHILE loop instead. Here is an exmaple:

-- Declare variables
DECLARE @li_row_count INT;
DECLARE @li_max_rows  INT;
DECLARE @col1         INT;
DECLARE @col2         INT;

-- Initialise variables
SET @li_row_count = 1;

-- Create temp table
CREATE TABLE #Test(id   INT IDENTITY (1,1) PRIMARY KEY,
                   col1 INT,
                   col2 INT);

-- Create second temp table
CREATE TABLE #Test2(id   INT IDENTITY (1,1) PRIMARY KEY,
                    col1 INT,
                    col2 INT,
                    col3 INT);

-- Populate second temp table
INSERT INTO #Test2 (col1, col2, col3) VALUES (1,1,1);
INSERT INTO #Test2 (col1, col2, col3) VALUES (2,2,2);
INSERT INTO #Test2 (col1, col2, col3) VALUES (3,3,3);
INSERT INTO #Test2 (col1, col2, col3) VALUES (4,4,4);
INSERT INTO #Test2 (col1, col2, col3) VALUES (5,5,5);

-- Populate temp table.  This should be populated from the selection criteria of your cursor
INSERT INTO #Test (col1, col2)
SELECT col1,
       col2
FROM   #Test2
WHERE  col3 < 5;
 
-- Get the maximum number of rows to process
SELECT @li_max_rows = COUNT(*) FROM #Test;

-- Loop through all the records
WHILE EXISTS (SELECT id,
                     col1,
                     col2
              FROM   #Test
              WHERE  id <= @li_max_rows
              AND    id  = @li_row_count)
BEGIN
   -- Do row processing here
   SELECT @col1 = col1,
          @col2 = col2
   FROM   #Test
   WHERE  id <= @li_max_rows
   AND    id  = @li_row_count;
  
  -- Print the outputs
   PRINT @col1;
   PRINT @col2;
 
   -- Increment counter for WHILE loop
   SELECT @li_row_count = @li_row_count + 1;

END;

-- Drop temp tables
DROP TABLE #Test;
DROP TABLE #Test2;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s