MSCCP0006

Fetch inside a loop is considered a complex pattern, this could degrade Snowflake performance

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

This warning is used to indicate that a FETCH statement was found inside a loop. This statement allows one to retrieve rows from a result set one at a time and perform some processing on each row.

This scenario could be considered a complex pattern when it involves processing a large number of rows or requires complex logic to handle the data. E.g. if the cursor fetch inside a loop construct requires joining multiple tables or performing complex calculations on the data, it can become difficult to manage and may not scale well for large datasets.

Code Example

Teradata

Input

REPLACE PROCEDURE teradata_fetch_inside_loop()
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE col_name VARCHAR(200);
    DECLARE col_int INTEGER DEFAULT 0;
    DECLARE cursor_var CURSOR FOR SELECT some_column FROM tabla1;
    WHILE (col_int <> 0) DO		
        FETCH cursor_var INTO col_name;
        SET col_int = col_int + 1;
    END WHILE;
END;

Output

Oracle

Input

CREATE PROCEDURE oracle_fetch_inside_loop
IS
  var1 table1.column1%TYPE;
  CURSOR cursor1 IS SELECT COLUMN_NAME FROM table1; 
BEGIN
  WHILE true LOOP
    FETCH cursor1 INTO var1;
    EXIT WHEN cursor1%NOTFOUND;
  END LOOP;  
END;

Output

SQL Server

Input

CREATE OR ALTER PROCEDURE transact_fetch_inside_loop
AS
BEGIN
    DECLARE cursor1 CURSOR
        FOR SELECT col1 FROM my_table;
    WHILE 1=0
    BEGIN
       FETCH NEXT FROM @cursor1 INTO @variable1;
    END
END;

Output

Recommendations

• One way to avoid turning into a complex pattern is to use set-based operations instead of a loop. This means using SQL statements like SELECT, UPDATE, and DELETE with appropriate WHERE clauses to operate on sets of rows at a time, rather than looping through individual rows. This is a workaround for this:

Oracle

CREATE OR REPLACE PROCEDURE cursor_fetch_inside_loop 
AS
  record_employee employees%rowtype;
  CURSOR emp_cursor IS SELECT * FROM employees;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO record_employee;
    EXIT WHEN emp_cursor%notfound;
    INSERT INTO new_employees VALUES (record_employee.first_name, record_employee.last_name);
  END LOOP;
  CLOSE emp_cursor;
END;

Could be used set-based operations.

CREATE OR REPLACE PROCEDURE cursor_fetch_inside_loop AS
BEGIN
  INSERT INTO new_employees (first_name, last_name)
  SELECT first_name, last_name FROM employees;
END;

Snowflake

Recommendations

• If you need more support, you can email us at [email protected]