Performance of SQL UDF's vs Javascript UDF's in SnowFlake

Question

We are trying to implement addition of interval values in Snowflake.Since there is no interval data type supported in Snowflake we are trying to implement the same using an UDF. We are converting the intervals into seconds and after getting the total seconds we are converting the total second into the required target interval type.

We have implemented the same logic using SQL UDF's as well as Javascript UDF.

Javascript UDF

    create or replace function js_interval(interval_1 string, interval_sign_1 double, interval_2 string, interval_sign_2 double)
  returns string
  language javascript
  strict
  as '     
  if ((INTERVAL_1.substring(0,1)=="-"))
  {
    var res = INTERVAL_1.split(":");
    var res2=(res[1]*-60+res[0]*3600);
    var res3= -1*res[2];
  }
  else
  {
    var res = INTERVAL_1.split(":");
    var res2=(res[1]*60+res[0]*3600);
    var res3= res[2];
  }
  if ((INTERVAL_2.substring(0,1)=="-"))
  {
     var res11 = INTERVAL_2.split(":");
     var res22=(res11[1]*-60+res11[0]*3600);
     var res33= -1*res11[2];
  }
  else
  {
     var res11 = INTERVAL_2.split(":");
     var res22=(res11[1]*60+res11[0]*3600);
     var res33= res11[2];
  }
  if(INTERVAL_SIGN_1 > 0)
  { 
    var result1= res2*1+res3*1;
  }
  else
  {
    var result1=(res2*1+res3*1)*-1;
  }
  if(INTERVAL_SIGN_2 > 0)
  { 
    var result2= res22*1+res33*1;
  }
  else
  {
    var result2=(res22*1+res33*1)*-1;
  }
  var final_sec= result1+result2;

  if (final_sec < 0 )
  {
    return "-"+String(Math.trunc(Math.abs(final_sec)/3600))+":"+String(Math.trunc(Math.abs(final_sec)/60)%60)+":"+String(Math.abs(final_sec)%60);
  }    
  else
  {
    return String(Math.trunc(Math.abs(final_sec)/3600))+":"+String(Math.trunc(Math.abs(final_sec)/60)%60)+":"+String(Math.abs(final_sec)%60);
  }
';

SQL UDF

 create or replace function addIntervalsH2S ( interval_1 varchar, interval_type_1 varchar , interval_sign_1 int, interval_2 varchar, interval_type_2 varchar, interval_sign_2 int )
  returns varchar 
  as
  'select trunc((((split_part(interval_1,\':\',1)*3600+split_part(interval_1,\':\',2)*60+split_part(interval_1,\':\',3))*interval_sign_1)+((split_part(interval_2,\':\',1)*3600+split_part(interval_2,\':\',2)*60+split_part(interval_2,\':\',3))*interval_sign_2))/3600)||\':\'||mod(trunc((((split_part(interval_1,\':\',1)*3600+split_part(interval_1,\':\',2)*60+split_part(interval_1,\':\',3))*interval_sign_1)+((split_part(interval_2,\':\',1)*3600+split_part(interval_2,\':\',2)*60+split_part(interval_2,\':\',3))*interval_sign_2))/60),60)||\':\'||mod((((split_part(interval_1,\':\',1)*3600+split_part(interval_1,\':\',2)*60+split_part(interval_1,\':\',3))*interval_sign_1)+((split_part(interval_2,\':\',1)*3600+split_part(interval_2,\':\',2)*60+split_part(interval_2,\':\',3))*interval_sign_2)),60)
  end';

The performance of Javascript based UDF for a nested level interval addition query is way better than the SQL based UDF.

What might be the reason that attributes to the huge performance difference?

It's very hard to answer without knowing more. What is "way better" performance-wise ? What exactly is the logic you expect, e.g. can you have intervals longer than 24h ? In general, it seems your SQL logic simply performs way more operations. It's possible that the optimizer will share the results of functions used multiple times, but it's hard to be sure. — Marcin Zukowski
– Marcin Zukowski, Commented May 22, 2018 at 23:15

Marcin Zukowski · Accepted Answer · 2018-05-30 18:07:55Z

2

We have identified an issue with subqueries in our SQL, where even in a simple query, e.g.

SELECT (SELECT expression1) + (SELECT expression2)

both subqueries are evaluated as separate execution "steps" (unless expression1/2 is very simple). Each "step" is like a full separate query, making it slow.

Since SQL UDFs are internally unfolded into subqueries, this results in each invocation of a SQL UDF that starts with SELECT to be evaluated using an extra step, making it significantly slower.

The solution? Try remove the SELECT keyword from the beginning of the SQL UDF, that should make it much faster. This is similar to rewriting the above query to

SELECT (expression1) + (expression2)

edited May 30, 2018 at 18:07

answered May 30, 2018 at 17:32

Marcin Zukowski

4,8491 gold badge25 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Performance of SQL UDF's vs Javascript UDF's in SnowFlake

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related