BigQuery Cost Optimization: Select Queries

Key Takeaways

welcome back to our series about bigquery cost optimization where today we'll be focusing on how you can optimize your queries specifically your select queries for both cost and performance as a reminder bigquery is a fully separated storage and compute Data Warehouse in lake house which means that you only pay for the storage as you need it and you only pay for the compute as you're analyzing data the art of query optimization considers both how you can optimize using storage and compute to derive the best query performance and query costs as a result of different options you may take so what can you do to actually optimize your different select query performance first let's think about how you can optimize different parts of a query because we'll take different methods depending on the type of performance that we need to optimize for and cost optimize for first is the i o or think of this as things like the scans and the shuffles that happen in our data this refers to the amount of data that we pass around either at the first scan is a part of the scan or a subsequent steps as a part of the shuffle next we need to consider how our data is joined at specific stages and aggregated as a part of a stage or subsequent stations this is how we can change our groupings for how much how many bytes do we pass through and how much CPU work did this consume last is how we can actually take this and then change our materialization or how much data we output as a result of our queries by reducing this we can improve our query performance because there's less data to actually write out to both storage and to a result pan so let's start optimizing the first and most common optimization for end user queries is changing the default user Behavior so we don't run select star across the table because bigquery storage is columnar It is Well optimized for running Aggregates but not returning large amounts of data across rows arbitrarily it's a good idea when running a query to only select the columns that you need or use select start accept which is a unique feature in bigquery to exclude the columns which you know you don't need this improves performance by reducing the number of bytes scanned at the outside and then also reduces the bytes that are past the subsequent stages this principle of scan reduction also applies to filter statements or where Clauses in your queries generally the sooner you can filter data the better and the more you can filter at the start the less data will be required at the end next let's look at how table designs can impact our query costs and performance we just discovered covered how we can use common performance optimization by reducing the bytes scanned as a part of our query syntax partitioning in clustering tables that are used by Common attributes is another way to help continue to reduce the byte scanned and sometimes improve performance of other things like joins you can use the recommender here which runs in our background of bigquery to propose table design options based on the queries that you run to help both improve performance and the resulting costs of both the table and your query reducing compute work is important to improving the cost and performance of bigquery one of the most common drivers of high compute is the use of functions like order by or rank where data must be sorted before it is outputted it's best to do these at the very end of a query as these operations typically have to run on a small subset of data and it typically can get pegged to a small set of slots to actually do the work you can use limits also on these to help reduce the amount of data that actually is returned so once the limit is reached the query can stop Computing which improves the performance of the query and how many slots you use at the end last it's important to consider how skew may impact your data SKU can be a difficult concept at times to understand but essentially this is how are we getting things into equal components to run across a distributed system eliminating or reducing skew in your queries can sometimes mean changing data filters or redesigning how the data is loaded into tables and stored so it is evenly distributed which means that many slots can work in parallel in order to accomplish the common output of getting your query done as fast as possible and at the lowest costs we hope you found these tips and tricks useful for optimizing your query costs and performance querying