CODEWITHSUNDEEP
google-bigquery
user1516770
user1516770

Reputation: 817

queries on union partitions runs much slower then on one table

following discussions in stackoverflow & with googlers recommendation, we worked hard to implement a daily partition strategy for bq tables, however, we are facing a problem that when there are more then ~30 partitions, it takes much longer ( can be 2-3 times more). so 3 months, is 90 partitions, even on small dataset of total 10 million rows, then it is twice slower then having one fact of 10m rows. what will happen when we will have 6 months?

why is that? what is the right approach?

Also, we see that GAE sometime have problem running big query size strings although the docs claim that the limit is very big.

Thanks a lot

Upvotes: 0

Views: 452

Answers (1)

Shanster
Shanster

Reputation: 73

I was facing a similar issue with transactional data logs. At first we tried using one huge table to store daily transaction data (for us that is data by the second). I also found something that said better performance can be achieved using table partitioning, however when trying to do it as you describe (by day) we got much worse performance than when we'd tried using one huge table.

Finally after trial and error, we discovered the best thing for us was to do a monthly table partition - this netted much better query performance (almost twice as fast!). Obviously I think this depends on your queries (for example if there are joins etc.) and also the specific requirements of your app. For us, a business rule was that we only store 3 years worth of customer data, therefore the maximum number of partitioned tables we will have at any one given time will be 36, but this may not be something that fits your app's needs.

Note - we're not on GAE, we're just using the raw BigQuery API via scripting, though I would expect performance to be better on GAE hosted apps.

I should also add that our average queries are around 30 million rows, but the data itself is not extremely verbose (lots of small strings and INTs)

Upvotes: 1

Related Questions