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
REPLACEPROCEDURE teradata_fetch_inside_loop()DYNAMIC RESULT SETS1BEGINDECLARE col_name VARCHAR(200);DECLARE col_int INTEGERDEFAULT0;DECLARE cursor_var CURSORFORSELECT some_column FROM tabla1;WHILE (col_int <>0) DO FETCH cursor_var INTO col_name;SET col_int = col_int +1;ENDWHILE;END;
CREATEORREPLACEPROCEDURE teradata_fetch_inside_loop ()RETURNSVARCHARLANGUAGESQLEXECUTEASCALLERAS$$BEGIN LET col_name VARCHAR(200); LET col_int INTEGERDEFAULT0; LET cursor_var CURSORFORSELECT 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;ENDLOOP;END;$$;
Oracle
CREATEPROCEDURE oracle_fetch_inside_loopIS var1 table1.column1%TYPE;CURSOR cursor1 ISSELECT COLUMN_NAME FROM table1; BEGINWHILE true LOOPFETCH cursor1 INTO var1; EXIT WHEN cursor1%NOTFOUND;ENDLOOP; END;
CREATEORREPLACEPROCEDURE oracle_fetch_inside_loop ()RETURNSVARCHARLANGUAGESQLEXECUTEASCALLERAS$$DECLARE var1 VARIANT; cursor1 CURSORFORSELECT COLUMN_NAME FROM table1;BEGINWHILE (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 ISNULL) THEN EXIT;ENDIF;ENDLOOP;END;$$;
SQL Server
CREATEORALTERPROCEDURE transact_fetch_inside_loopASBEGINDECLARE cursor1 CURSORFORSELECT col1 FROM my_table;WHILE1=0BEGINFETCHNEXTFROM @cursor1 INTO @variable1;ENDEND;
CREATEORREPLACEPROCEDURE transact_fetch_inside_loop ()RETURNSVARCHARLANGUAGESQLEXECUTEASCALLERAS$$DECLARE/*** MSC-ERROR - MSCEWI4038 - SNOWFLAKE SCRIPTING CURSOR ROWS ARE NOT MODIFIABLE ***/ cursor1 CURSORFORSELECT col1 FROM my_table;BEGINWHILE (1=0) LOOP --** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
FETCH CURSOR1 INTO :VARIABLE1;ENDLOOP;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:
CREATEORREPLACEPROCEDURE cursor_fetch_inside_loop AS record_employee employees%rowtype;CURSOR emp_cursor ISSELECT*FROM employees;BEGINOPEN emp_cursor;LOOPFETCH emp_cursor INTO record_employee; EXIT WHEN emp_cursor%notfound;INSERT INTO new_employees VALUES (record_employee.first_name, record_employee.last_name);ENDLOOP;CLOSE emp_cursor;END;
Could be used set-based operations.
CREATEORREPLACEPROCEDURE cursor_fetch_inside_loop ASBEGININSERT INTO new_employees (first_name, last_name)SELECT first_name, last_name FROM employees;END;
CREATEORREPLACEPROCEDURE cursor_fetch_inside_loop ()RETURNSVARCHARLANGUAGESQLEXECUTEASCALLERAS$$DECLARE record_employee OBJECT := OBJECT_CONSTRUCT(); emp_cursor CURSORFORSELECT OBJECT_CONSTRUCT(*) sc_cursor_record FROM employees;BEGINOPEN 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 ISNULL) THEN EXIT;ENDIF;INSERT INTO new_employeesSELECT :record_employee:FIRST_NAME, :record_employee:LAST_NAME;ENDLOOP;CLOSE emp_cursor;END;$$;
Could be used set-based operations.
CREATEORREPLACEPROCEDURE cursor_fetch_inside_loop ()RETURNSVARCHARLANGUAGESQLEXECUTEASCALLERAS$$BEGININSERT INTO new_employees(first_name, last_name)SELECT first_name, last_name FROM employees;END;$$;