title: Making Snowflake Javascript based procedure query faster
body: <p>I have a stored procedure that I just converted to Snowflake Javascript from PL/SQL.  It inserts about 100 records a minute.  The total record count is about 700.  Because it is so difficult to know where a problem is in Snowflake, I insert log statements as the overall functionality progresses.  I also push messages to an array that gets returned at the bottom.  However, I do the insert into log table type things in PL/SQL and it barely makes a performance difference.  I'll admit that my progress loading slows down the process, but am doubtful that it's the primary contributor.</p>
  <p>The script makes a table that, given a date, shows the fiscal quarter that it corresponds to.  This is helpful for other queries not shown.  I have a simple loop that goes from the beginning of the first quarter to the end of the last and puts the corresponding quarter in the lookup table.</p>
  <p>It took 9 minutes to run as written, but in Oracle, takes less than a second.</p>
  <p>I'd like to know how to make this run faster:</p>
  <pre><code>create or replace procedure periodic_load()
      RETURNS varchar
      LANGUAGE javascript
      execute as owner 
      as 
      $$
      var result = &quot;&quot;;
      var messages = new Array();
      try {
          /**
          Constants shared between functions
          */
          var SINGLE_QUOTE_CHAR=&quot;'&quot;;
          var DOUBLE_QUOTE_CHAR=&quot;\&quot;&quot;;
          var COMMA_CHAR=&quot;,&quot;;
          var LEFT_PARENTHESIS=&quot;(&quot;;
          var RIGHT_PARENTHESIS=&quot;)&quot;;
          var ESCAPED_SINGLE_QUOTE_CHAR=&quot;\\'&quot;;
          var ESCAPED_DOUBLE_QUOTE_CHAR=&quot;\\\&quot;&quot;;
          var CONSOLE_LOG_USED = true;
          var IS_SNOWFLAKE = false; 
  
  
          /*
          Execute Snowflake SQL or simulate the execution thereof
          @parmam  sqlTextIn,binds...
          sqlTextIn: String of the sql command to run.
          binds: zero or more parameters to bind to the execution of the command.
          */
          function execute_with_log() {
              var result = null;
              messages.push('@@@'+&quot;execute_with_log()&quot;);
              messages.push('@@@'+&quot;EXECUTE_WITH_LOG(BP1)&quot;);
  
              var argumentsArray = Array.prototype.slice.apply(arguments); 
              var sqlTextIn = argumentsArray[0];
              messages.push('@@@'+'EXECUTE_WITH_LOG argument count: '+arguments.length);
              if(!IS_SNOWFLAKE) { 
                  messages.push('@@@'+ &quot;EXECUTE_WITH_LOG(BP2)&quot;);
                  console.log('SKIPPING SNOWFLAKE SQL: '+sqlTextIn);
              } else {
                  messages.push('@@@'+ &quot; EXECUTE_WITH_LOG(BP3)&quot;);
                  var statementResult;
                  var logMessage = sqlTextIn;
                  if(argumentsArray.length==1) {
                      messages.push('@@@'+ &quot; EXECUTE_WITH_LOG(BP4)&quot;);
                      messages.push('@@@'+&quot; ** NO BIND PARAMETERS DETECTED **&quot;);
                  } else {
                      messages.push('@@@'+ &quot; EXECUTE_WITH_LOG(BP5)&quot;);
                      for(var bindParmCounter = 1; bindParmCounter &lt; argumentsArray.length; bindParmCounter++) {
                          messages.push('@@@'+&quot; ,&quot;+argumentsArray[bindParmCounter]);    
                      }
                  }
                  messages.push('@@@'+ &quot; EXECUTE_WITH_LOG(BP6)&quot;);
                  log_message('I',logMessage);
                  if(argumentsArray.length===1) {
                      messages.push('@@@'+ &quot; EXECUTE_WITH_LOG(BP7)&quot;);
                      statement = snowflake.createStatement( { sqlText: sqlTextIn });
                  } else {
                      messages.push('@@@'+ &quot; EXECUTE_WITH_LOG(BP8)&quot;);
                      var bindsIn = argumentsArray.slice(1,argumentsArray.length);
                      for(var bindParmCounter = 0; bindParmCounter &lt; bindsIn.length; bindParmCounter++) {
                          messages.push('@@@bindsIn['+bindParmCounter+&quot;]=&quot;+bindsIn[bindParmCounter]);    
                          messages.push('@@@bindsIn['+bindParmCounter+&quot;] type =&quot;+bindsIn[bindParmCounter].getName());    
                      }
                      statement = snowflake.createStatement(
                          {
                              sqlText: sqlTextIn,
                              binds: bindsIn
                          }
                      );
                  }
  
                  messages.push('@@@'+ &quot; EXECUTE_WITH_LOG(BP9) sqlTextIn=&quot;+sqlTextIn);
                  result = statement.execute();
                  messages.push('@@@'+ &quot; After execute BP10 =&quot;);
                  commit();
                  messages.push('@@@'+ &quot; After commit BP11 =&quot;);
  
              }
              return result;
          }
  
          function commit() {
              messages.push('@@@'+ &quot; commit&quot;);
              statement = snowflake.createStatement(
                  {
                      sqlText: 'commit'
                  }
              );
              statement.execute();
              return messages;
          }
  
          function log_message(severity,message) {
              messages.push('@@@'+&quot;log_message(severity,message): severity=&quot;+severity+&quot; message=&quot;+message);
              var result = null;
              if(!IS_SNOWFLAKE) {
                  console.log(severity+&quot;: &quot;+message);
                  messages.push('@@@'+severity+&quot;: &quot;+message);
              } else {
                  var record = {'severity': severity,'date_time': {value: 'current_timestamp::timestamp_ntz',useQuote:false},message:message};
                  try {
                      var escapeStep1=message.replaceAll(SINGLE_QUOTE_CHAR,ESCAPED_SINGLE_QUOTE_CHAR);
                      var escapeStep2=escapeStep1.replaceAll(DOUBLE_QUOTE_CHAR,ESCAPED_DOUBLE_QUOTE_CHAR);
                      quotedValue=SINGLE_QUOTE_CHAR+escapeStep2+SINGLE_QUOTE_CHAR;
  
                      var quotedSeverity = SINGLE_QUOTE_CHAR+severity+SINGLE_QUOTE_CHAR;
  
                      var sql_command = &quot;insert into LOG_MESSAGES(severity,date_time,message) values(&quot;+quotedSeverity+&quot;,current_timestamp::timestamp_ntz,&quot;+quotedValue+&quot;)&quot;;
                      statement = snowflake.createStatement( { sqlText: sql_command});
  
                      var sql_command = &quot;commit&quot;;
                      statement = snowflake.createStatement( { sqlText: sql_command});
                  } catch(error) {
                      messages.push('@@@'+'FAILURE: '+error);
                  }
              }
              return result;
          }
  
          function truncate_table(tableName) {
              messages.push('@@@'+&quot;(truncate_table()&quot;);
              var result = execute_with_log(&quot;truncate table &quot;+tableName); 
              messages.push('@@@'+'I','End truncate_table()');
              return result;
          }
  
          function fql() {
              messages.push('@@@'+&quot;begin fql()&quot;);
              log_message('I','Begin fql()');
              var table_name='fiscal_quarter_list';
              truncate_table(table_name);
              execute(
                  &quot;insert into fiscal_quarter_list (fiscal_quarter_id,fiscal_quarter_name,fiscal_year,start_date,end_date,last_mod_date_stamp) (&quot;
                  +&quot;    select fiscal_quarter_id,fiscal_quarter_name,fiscal_year,min(start_date) start_date,max(end_date) end_date,current_date from cdw_fiscal_periods cfp&quot;
                  +&quot;    where (cfp.start_date &gt;= add_months(sysdate(),-24) and  sysdate() &gt;= cfp.end_date ) or &quot;
                  +&quot;          (cfp.start_date &lt;= sysdate() and sysdate() &lt; cfp.end_date)  &quot;
                  +&quot;    group by fiscal_quarter_id,fiscal_quarter_name,fiscal_year &quot;
                  +&quot;    order by fiscal_quarter_id desc &quot;
                  +&quot;    fetch first 8 rows only &quot;
                  +&quot;)&quot;
              );
              log_message('I','End fql()');
          }
  
  
          /*
          Function to increment a Date object by one standard day
          Sourced from https://stackoverflow.com/questions/563406/add-days-to-javascript-date
          */
          function addDaysInJs(dateIn, days) {
              var result = new Date(dateIn);
              result.setDate(result.getDate() + days);
              return result;
          }
  
  
          function dtfq()  {
              messages.push('@@@'+&quot;dtfq()&quot;);
              tableName = 'date_to_fiscal_quarter';
              var firstDate;
              var runningDate;
  
              log_message('I','Begin dtfq');
              truncate_table(tableName);
              var result = null;
              var resultSet = execute_with_log(&quot; SELECT FISCAL_QUARTER_ID, FISCAL_QUARTER_NAME,try_to_date(START_DATE) as START_DATE, try_to_date(END_DATE)  as END_DATE&quot;
                                                      + &quot; FROM FISCAL_QUARTER_LIST &quot;
                                                      + &quot; ORDER BY START_DATE  &quot;);
  
              log_message('D','resultSet ='+resultSet);
              log_message('D','resultSet typeof='+typeof resultSet);
              while(resultSet.next()) {
                  messages.push('@@@'+&quot;bp1 dtfq() loop start_date=&quot;+resultSet.getColumnValue(&quot;START_DATE&quot;)+&quot; end_date=&quot;+resultSet.getColumnValue(&quot;END_DATE&quot;));
                  firstDate = resultSet.getColumnValue(&quot;START_DATE&quot;);
                  lastDate = resultSet.getColumnValue(&quot;END_DATE&quot;);
                  runningDate=new Date(firstDate);
                  lastDate = new Date(lastDate);
                  log_message('D','Start date='+firstDate);
                  while (runningDate &lt;= lastDate) {
                      var fiscalQuarterId=resultSet.getColumnValue(&quot;FISCAL_QUARTER_ID&quot;)
                      var fiscalQuarterName=resultSet.getColumnValue(&quot;FISCAL_QUARTER_NAME&quot;)
                      messages.push('@@@'+&quot;bp2 dtfq() runningDate=&quot;+runningDate+' fiscalQuarterId='+fiscalQuarterId+' fiscalQuarterName='+fiscalQuarterName);
                      log_message('D','Fiscal quarter id='+fiscalQuarterId);
                      /*
                      execute_with_log(&quot; insert into sc_hub_date_to_fiscal_quarter(date_stamp,) &quot;
                                              +&quot; values(try_to_date(?)) &quot;
                                              ,runningDate.toISOString());
                                              */
                      execute_with_log(&quot; insert into sc_hub_date_to_fiscal_quarter(date_stamp,fiscal_quarter_id,fiscal_quarter_name) &quot;
                                              +&quot; values(?,?,?)&quot;
                                              ,runningDate.toISOString()
                                              ,fiscalQuarterId
                                              ,fiscalQuarterName);
                                           
                      runningDate = addDaysInJs(runningDate, 1);
                  }
  
              }
  
              log_message('I','End dtfq Success');
              return result;
          }
  
          /*
          Execute Snowflake SQL or simulate the execution thereof
          @parmam  sqlTextIn,binds...
          sqlTextIn: String of the sql command to run.
          binds: zero or more parameters to bind to the execution of the command.
          */
          function execute() {
              messages.push('@@@'+&quot;execute():&quot;);
         
              var result = null;
              var argumentsArray = Array.prototype.slice.apply(arguments); 
              var sqlTextIn = argumentsArray[0];
              if(!IS_SNOWFLAKE) { 
                  console.log('SKIPPING SNOWFLAKE SQL: '+sqlTextIn);
                  messages.push('@@@'+'SKIPPING SNOWFLAKE SQL: '+sqlTextIn);
              } else {
                  messages.push('@@@'+'USING SNOWFLAKE SQL: '+sqlTextIn);
                  var statementResult;
                  if(argumentsArray.length&gt;2) {
                      messages.push('@@@'+'Has bind arguments: ');
                      var bindsIn = argumentsArray.slice(2,argumentsArray.length);
                      statement = snowflake.createStatement(
                          {
                          sqlText: sqlTextIn,
                          binds: bindsIn
                          }
                      );
                  } else {
                      messages.push('@@@'+'Has no bind arguments: ');
                      messages.push('@@@'+'###sqlText='+sqlTextIn+'###');
                      statement = snowflake.createStatement( { sqlText: sqlTextIn });
                  }
                  result = statement.execute();
                  messages.push('@@@'+'statement.execute succeeded');
                  log_message('I',sqlTextIn);
              }
              return result;
          }
          
  
          String.prototype.replaceAll = function(target, replacement) {
            return this.split(target).join(replacement);
          };
  
          Object.prototype.getName = function() { 
             var funcNameRegex = /function (.{1,})\(/;
             var results = (funcNameRegex).exec((this).constructor.toString());
             return (results &amp;&amp; results.length &gt; 1) ? results[1] : &quot;&quot;;
          };
  
          dtfq(); 
      } catch(error) {
          messages.push('@@@'+error);
      } finally {
          result = messages.join(&quot;\n&quot;);
      }
  
      
  
      return result;
      $$
      ;
  
  
  call periodic_load()
  
  
  
  
  </code></pre>