Why does my Google BigQuery query took so long?

Question

It took over 18 minutes to run the following query with our test dataset:

SELECT count(distinct S1.visitorId, 50000) as returningVisitors, 
STRFTIME_UTC_USEC(UTC_USEC_TO_DAY(PARSE_UTC_USEC(S1.timeStamp)), '%Y-%m-%d') AS day,
S1.dimension1, S1.dimension2
FROM [myDataset.MyTable] as S1 
JOIN EACH [myDataset.MyTable] as S2 on S1.visitorId= S2.visitorId
WHERE UTC_USEC_TO_DAY(PARSE_UTC_USEC(S1.timeStamp)) < UTC_USEC_TO_DAY(NOW()) and
S2.timeStamp < STRFTIME_UTC_USEC(UTC_USEC_TO_DAY(PARSE_UTC_USEC(S1.timeStamp)), '%Y-%m-%d') 
GROUP EACH BY S1.dimension1, S1.dimension2, day 
ORDER BY S1.dimension1, S1.dimension2, day;

At the end I got the following message in the web browser: "Query complete (1112.1s elapsed, 1.62 MB processed)"

I wonder why it took so long. I usually have much faster results with BigQuery.

The query does a JOIN on the same table to get number of returning visitors for each day and dimensions. I expected the query to take maybe 5-6 minutes but not 18 minutes especially since the table is not that big.

My table as around 31000 rows and has a total size of 4.25 Mb. My job id is: job_b657aceeb1004994b0b0332d461cdcd2

I edited the details on the table that was queried. The table is even much smaller than I thought: 31000 rows and total size of 4.25Mb. — YABADABADOU

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2013-10-09T01:04:51

Is this query still taking that long to process? If it only happened once, the "why" is probably a rare internal performance problem.

Tell me if I'm getting this right: The only reason you are self joining the table, is to check if the user has been there before? In this case you are generating an exponentially growing (am I using this word right?) number of combinations, without the need to. The query only refers to S2 once, to check that it's less than the current row's timestamp day.

What if you replace:

JOIN EACH [myDataset.MyTable] as S2 on S1.visitorId= S2.visitorId

with:

JOIN EACH 
(SELECT visitorId, MIN(timeStamp) timeStamp FROM [myDataset.MyTable] GROUP EACH BY 1) S2
ON S1.visitorId= S2.visitorId

to get:

SELECT count(distinct S1.visitorId, 50000) as returningVisitors, 
STRFTIME_UTC_USEC(UTC_USEC_TO_DAY(PARSE_UTC_USEC(S1.timeStamp)), '%Y-%m-%d') AS day,
S1.dimension1, S1.dimension2
FROM [myDataset.MyTable] as S1 
JOIN EACH 
(SELECT visitorId, MIN(timeStamp) timeStamp FROM [myDataset.MyTable] GROUP EACH BY 1) S2
ON S1.visitorId= S2.visitorId    WHERE UTC_USEC_TO_DAY(PARSE_UTC_USEC(S1.timeStamp)) < UTC_USEC_TO_DAY(NOW()) and
S2.timeStamp < STRFTIME_UTC_USEC(UTC_USEC_TO_DAY(PARSE_UTC_USEC(S1.timeStamp)), '%Y-%m-%d') 
GROUP EACH BY S1.dimension1, S1.dimension2, day 
ORDER BY S1.dimension1, S1.dimension2, day;

?

Some notes:

Try to replace NOW() with a concrete datetime - that way your query can be cached.

Why does my Google BigQuery query took so long?

1 Answers