Why is BigQuery so slow on non-large data sizes?

Question

We have found BigQuery to work great on data sets larger than 100M rows, where the 'initialization time' doesn't really come into effect (or is negligible compared to the rest of the query).

However, on anything under that, the performance is quite slow and poor, which makes it (1) ill-suited to working in an interactive BI tool; and (2) inferior to other products, such as Redshift or even ElasticSearch where the data size is under 100M rows. Actually, we had an engineer at our organization that was evaluating a technology for doing queries on data sizes between 1M and 100M rows for an analytics product that has about 1000 users, and his feedback was that he could not believe how slow BigQuery was.

Without a defense of the BigQuery product, I was wondering if there were any plans on improving:

The speed of BigQuery -- especially its initialization time -- on queries of non-massive data sets?
Will BigQuery ever be able to deliver sub-second response times on 'regular' queries (such as a simple aggregation group by) on datasets under a certain size?

Without understanding the in's and out's of how BigQuery is programmed, I wouldn't be surprised if most of that 3sec is based on initiation time. Do you get the same result for a table with 10 rows? Hadoop (before Yarn was implemented) used to spend around 3sec setting up a JVM with each query. — Ed Baker
It's time spent on metadata/initiation as Ed guessed, but actual execution time is very small. We have work in progress that will address this, but some of the changes are complicated and will take a while. — Elliott Brossard
@ElliottBrossard thanks, would you be interested/able to share any of the broad details in an answer? One of the downsides of this is on benchmarks Redshift comes out much faster on smaller data sizes (but is crushed on large actual big-data sizes), which is a big marketing advantage for Amazon when publishing comparisons! — David542
It's hard to say anything useful without going into internal details, unfortunately. You can imagine, though, that in its infancy, BigQuery could have central systems for managing jobs, metadata, etc. in a manner that performed very well for all N_0 entities using the service. Once you get to N_1 entities, however, it may be necessary to rearchitect some things to make them have as little latency as possible. — Elliott Brossard
@ElliottBrossard thanks, any estimate on when that change will be implemented? A couple months? end of 2017? etc. — David542

Elliott Brossard Elliott Brossard · Accepted Answer · 2017-03-01T11:54:31

It's time spent on metadata/initiation, but actual execution time is very small. We have work in progress that will address this, but some of the changes are complicated and will take a while.

You can imagine that in its infancy, BigQuery could have central systems for managing jobs, metadata, etc. in a manner that performed very well for all N₀ entities using the service. Once you get to N₁ entities, however, it may be necessary to rearchitect some things to make them have as little latency as possible. For notification about new features--which is also where we would announce API improvements related to start-up latency--keep an eye on our release notes, which you can also subscribe to as an RSS feed.

Why is BigQuery so slow on non-large data sizes?

2 Answers