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

Input

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;

Output

OUT -> Teradata_01.sql

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "tabla1" **
CREATE OR REPLACE PROCEDURE teradata_fetch_inside_loop ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/04/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        col_name VARCHAR(200);
        col_int INTEGER DEFAULT 0;
    BEGIN
         
         
        LET cursor_var CURSOR
        FOR
            SELECT
                some_column FROM
                tabla1;
                WHILE (col_int <> 0) LOOP
            --** SSC-PRF-0003 - 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

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;

Output

OUT -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE oracle_fetch_inside_loop ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    var1 VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-OR0129 - TYPE ATTRIBUTE 'table1.column1%TYPE' COULD NOT BE RESOLVED, SO IT WAS TRANSFORMED TO VARIANT ***/!!!;
    cursor1 CURSOR
    FOR
      SELECT COLUMN_NAME FROM
        table1;
  BEGIN
    WHILE (true) LOOP
      --** SSC-PRF-0003 - 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

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;

Output

OUT -> SqlServer_01.sql

CREATE OR REPLACE PROCEDURE transact_fetch_inside_loop ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        --** SSC-FDM-TS0013 - SNOWFLAKE SCRIPTING CURSOR ROWS ARE NOT MODIFIABLE **
        cursor1 CURSOR