Slow connection to BigQuery

Matillion ETL
1

Hi,

 

Our Matillion is hosted in GCP VM, and it loads many files to BigQuerry.

I found out that every component using BigQuery takes at least 2 seconds to run. Same results both in DEV and PROD environments.

 

For example, if I create a dummy orchestration job that runs 8 "select CURRENT_DATETIME() AS Now" in sequence, it runs for 22 seconds. More than 2 seconds to perform a single select, which is really too slow in our context.

0694G00000GwwLxQAJ_0D54G00007zHIxdSAG
0694G00000GwwLxQAJ_0D54G00007zHIxdSAG1016×102 11.6 KB

A dummy orchestration job that runs 8 bash scripts (echo “Hello world”) runs for 1 second, so I understand the problem is not directly on Matillion VM.

0694G00000GwwEPQAZ_0D54G00007zHIxdSAG

Any idea why we face this slowness on querying BQ from Matillion ? Maybe did I miss something obvious here ?

 

Thanks for your help,

2

I think this is the nature of BigQuery - there's a fixed overhead that means it may not be the fastest when doing small operations, which its not optimal for.

3

Yes, looks like you're right.

I made a few more checks, and running the same sequence of 8x SELECT CURRENT_DATETIME() AS Now;" in Google bigquery takes long too.

Thanks for the reply.

4

I agree with @Lightbody​. This isn't the first time I have heard there is more overhead in the connection and prep of running a query in GBQ than other Cloud Data Warehouses.