BigQuery Scripting - Slow

Question

So, the BigQuery Scripting feature came out and I thought of giving it a try.

I understand it is still in beta and being tested. However, to get a feel of it, I ran this small simple loop of 20k cycles a bunch of times, and each time it took between 5 and 10 minutes (and sometimes more) to complete. Sometimes, I just had to cancel the job because it was taking forever.

declare n int64;
declare i int64;
declare k float64;

set i = 0;
set n = 20000;
set k = rand();

loop
  set i = i + 1;
  if i >= n then leave;
  else set k = k*rand();
  end if;
end loop;

select k;

I am wondering if I am doing anything incorrect here, or it's just that it is not as performant yet.

NOTE: Here is one of the job ids: music-178807:US.bquxjob_366fc627_16da33c0ee1

Can you try and be a little more specific? Using wording like "several minutes" and "forever" can be ambiguous. Also, some BigQuery job ids would be helpful for the Google engineers no doubt :) — Graham Polley
Okay. So, it has been taking variable times between 5 and 10 minutes (and sometimes more). The words like "forever/slow" are actually comparative to the small scale of script :-) — khan
Understood, but remember that engineers like specific details :) Can you also provide job ids? — Graham Polley

Elliott Brossard Elliott Brossard · Accepted Answer · 2019-10-07T00:27:16

Scripting in BigQuery is intentionally not nearly as fast as running this type of code in some other language. The expectation is that people will want to use scripting to tie together multiple queries, not to multiply numbers in a loop. Notice also that there is no additional cost for scripting, whereas high performance would probably have to come with a price tag.

BigQuery Scripting - Slow

1 Answers