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

CREATE OR REPLACE PROCEDURE teradata_fetch_inside_loop ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        LET col_name VARCHAR(200);
        LET col_int INTEGER DEFAULT 0;
        LET cursor_var CURSOR
        FOR
            SELECT some_column FROM tabla1;
            WHILE (col_int <> 0) LOOP
                  --** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
                FETCH cursor_var INTO col_name;
                col_int := col_int + 1;
            END LOOP;
    END;
$$;

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

CREATE OR REPLACE PROCEDURE oracle_fetch_inside_loop ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    var1 VARIANT;
    cursor1 CURSOR
    FOR
      SELECT COLUMN_NAME FROM table1;
  BEGIN
    WHILE (true) LOOP
      --** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
      FETCH cursor1 INTO
        :var1;
      IF (var1 IS NULL) THEN
        EXIT;
      END IF;
    END LOOP;
  END;
$$;

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

CREATE OR REPLACE PROCEDURE transact_fetch_inside_loop ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        /*** MSC-ERROR - MSCEWI4038 - SNOWFLAKE SCRIPTING CURSOR ROWS ARE NOT MODIFIABLE ***/
        cursor1 CURSOR FOR SELECT col1 FROM my_table;
    BEGIN
        WHILE (1=0) LOOP
            --** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
            FETCH CURSOR1 INTO :VARIABLE1;
        END LOOP;
    END;
$$;

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

CREATE OR REPLACE PROCEDURE cursor_fetch_inside_loop ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    record_employee OBJECT := OBJECT_CONSTRUCT();
    emp_cursor CURSOR
    FOR
      SELECT
        OBJECT_CONSTRUCT(*) sc_cursor_record FROM
        employees;
  BEGIN
    OPEN emp_cursor;
    LOOP
      --** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
      FETCH emp_cursor INTO
        :record_employee;
      IF (record_employee IS NULL) THEN
        EXIT;
      END IF;
      INSERT INTO new_employees
      SELECT
        :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 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  BEGIN
    INSERT INTO new_employees(first_name, last_name)
    SELECT first_name, last_name FROM employees;
  END;
$$;

Recommendations

• If you need more support, you can email us at [e-mail]