CODEWITHSUNDEEP
stored-proceduresgoogle-bigquery
Bhawani shankar Panda
Bhawani shankar Panda

Reputation: 353

Why stored procedures in GCP BigQuery are so slow?

I am trying to create a list with last 13 months date in this format '2019-11','2019-10' and so on in GCP BigQuery.

I have written this stored procedure. But it's taking more than 1 minute to complete this simple program. Why is it so slow ?

DECLARE length INT64;
DECLARE filter STRING;
BEGIN
  CREATE TEMP TABLE Months
  (
    x STRING
  );
  SET length = 13;
  WHILE(length > 0) DO
    SET filter = (SELECT FORMAT_DATE("%Y-%m",DATE_SUB(CURRENT_DATE(), INTERVAL length-1 MONTH)));
    INSERT INTO Months VALUES(filter);
    SET length = length-1;
  END WHILE;
END;

SELECT * FROM Months ORDER BY x desc;

Just edited my question to show the poor performance of BigQuery. For the below procedure which doesn't do anything except iterating 1000 times. It takes 14 secs to complete.

DECLARE length INT64;
BEGIN
  SET length = 1000;
  WHILE (length > 0) DO
    SET length = length -1;
  END WHILE;
END;

Upvotes: 2

Views: 684

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173121

It is recommended to think set-based whenever possible - and use scripting only to "orchestrate" multiple statements when otherwise is not possible.

So, for example, your script to generate month temp table can be re-written as set based as in below example 

#standardSQL
SELECT FORMAT_DATE("%Y-%m", day) x 
FROM UNNEST(
  GENERATE_DATE_ARRAY(DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH), CURRENT_DATE(), INTERVAL 1 MONTH)
) day
ORDER BY x DESC

Try and see the difference
As Pentium10 mentioned, query times is not linear - so if you replace 12 months with let's say 1200 months - query time will be same (almost) as for 12

Upvotes: 4

Related Questions