SSC-PRF-0003

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

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

IN -> Teradata_01.sql
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;

Oracle

IN -> Oracle_01.sql
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;

SQL Server

IN -> SqlServer_01.sql
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;

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:

IN -> Oracle_02.sql
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;