Simple queries on BQ fetching time is too long (~40s)

[alma_rennert]

Hello,

We simplified our production queries, so our queries are in the following format: "select {} from {} where {}". 

Despite that, for a query that running over small table (processing 100MB), we can see that the calculation time is only 2sec, but fetching time takes very long time- around 35sec (just to get results to the console/NodeJS code). 

Any advice how to optimize this? thanks.

[ms4446]

There are several potential strategies to optimize the fetching time of BigQuery queries:

1. Caching: BigQuery caches the results of each query you run. If you run the same query again, BigQuery will return the cached result, which is much faster than running the query again. However, this only works if the data and the query haven't changed. You can enable caching by setting the useQueryCache option to true when you run your query.

2. Use Partitioned Tables: Partitioning your data can improve query performance, and reduce costs, by limiting the amount of data read by a query. When you partition a table, BigQuery organizes the data by a specific column, like a DATE or TIMESTAMP, or by range, which allows BigQuery to only scan the relevant partitions of your table.

3. Use Clustered Tables: Clustering reorganizes your data based on the contents of one or more columns in the table’s schema. The clustered columns are used to co-locate related data. When you cluster a table, BigQuery sorts the data based on the values in the clustered column and organizes the data into blocks. When you run a query, BigQuery only reads the blocks that contain the data for the clustered column values that the query is filtering on.

4. Fetch less data: The more data you fetch, the longer it will take. If you can reduce the amount of data fetched by filtering more aggressively or selecting fewer columns, you may be able to reduce the fetching time.

5. Investigate network latency: If your query execution time is short, but fetching time is long, it could be due to network latency between BigQuery and the machine where you're running your queries. You might want to investigate this aspect, possibly by testing queries from a different network or machine.

6. Parallelize your queries: If possible, try to run multiple queries at the same time. BigQuery is designed to handle large amounts of data, so it can often run multiple queries more efficiently than one large query.