Now, here's one of the modules that everybody loves. It's how to make your queries run faster and save on data processing costs. In this performance optimization module, we'll cover the key types of work that BigQuery does in the back-end and how you can avoid falling into performance traps. Then we'll cover one of the most common data traps, which is having too much data skewed to a too few values and how filtering can be your best friend here. Last up is your best tool for analyzing and debugging performance issues, the Query Explanation map. There are four key areas that govern a lot of the characteristics of BigQuery performance. Let's take a look at each of them in turn and see some examples. First and foremost is how much data that you're reading and writing at a BigQuery, you have your input and output. Second is that communication between your workers or slots, it's done via the shuffle mechanism that we talked a little bit about before in the architecture lecture. Third is highly computational work like large functions, UDFs, mathematical operations; that's how much of the CPU you're using. The fourth, which is my favorite, is the SQL syntax. Is there a more efficient ways to write your queries? Or what are some of those pitfalls that we can avoid when we're writing that SQL? Let's look at each of those. First up is input and output, how much data we're reading and then how much data we're writing. As you might have expected, Select Star makes the list here. We only want to use the columns that we're actually going to use. If you want a quick preview of that dataset, again, use the preview mechanic that's built into the metadata in the BigQuery UI. Second, BigQuery performs the best when your data is denormalized. That means instead of having a lot of small tables and doing joins to bring the data back together, have all those data tables be combined into one massive table or a denormalized table as we saw in the architecture lecture, and use things like nested and repeated fields. Instead of having orders in one table and customers in the other, consider actually bringing those together in a parent-child relationship through that nested and repeated structure that we looked at it a little bit earlier. Lastly, as you saw on the merging and union of datasets, try to use those really granular suffixes in your table wildcards to get the most specificity out of your data. As an example here, if you remember the GSOD or GSOD tables for weather, having something like GSOD star is little bit less specific than saying GSOD 2.0 star to get only years from the year 2000 on forward. Next, let's talk a little bit about BigQuery native storage versus the external data connections that we discussed a little bit more ingesting those datasets. Let's cover some of the downfalls of external data connections. First, they'll never be able to be cached because caching is a function of BigQuery-managed storage itself. Second, live edits to underlying data sources. If there's a spreadsheet that you're relying on data from just to quickly pull that into BigQuery, if you have somebody that's editing that spreadsheet live at the same time, you could create potential race conditions and BigQuery is not going to know which dataset is the latest to bring in from that spreadsheet versions. Lastly, BigQuery has a lot of performance tweaks under the hood that you might not even realize. When you're writing things like a long query, if you filter your dataset all the way at the end, what BigQuery might actually do is it recognizes that filter, it can actually be performed earlier, and it'll do something that's called predicate push down. Without even you knowing it, it'll actually bump up your WHERE clause much earlier in the query and then filter that data before it gets processed a little bit. Now, keep in mind, there are particular use cases where you want to use an external data connection, like grabbing in ad hoc data and then performing a one time analysis on it, much like in your extract, transform, and load step, and then ultimately storing it, so you don't have to continuously do that, but keep in mind the performance hits in the trade-offs that you're making there. Next up is the shuffle mechanic. One of the most interesting things behind the hood is how those workers communicate to each other to break apart your query and assign that work to a lot of different workers or slots behind the scenes. Now, we can do to make their lives easier is pre filter your data before doing massive joins. If you have 80 percent of your records are null, go ahead and rub off those rows even before passing that work to the workers, and that saves a lot of reads and it saves a lot of communication time and passing those data between each of the different slots. Now, one of the things I'll highlight here is that you can identify potential data skew if you have one of your workers that's getting particularly bottlenecked. We'll take a look at how you can identify that and recognize that a little bit down the road. Here's a few fun querying examples. When we talked about the WITH clause, we said use it liberally and help break apart those complex queries, but one of the caveats or drawbacks to that is you don't want to use a bunch of WITH clause in place of materializing the results of those queries into permanent storage. Here you see in the query on the left, we're using a WITH clause to define a join against the IRS 2015 filings data against the organizational details table. If you ran this query more than once, you're continuously doing that joint over and over again, it's much more performance just to store Lines 6 through 13 as a permanent table and then refer to that later. Now, the main takeaway with WITH clauses is that the results of those queries or those subqueries that you've named in that WITH clause like Line 6 and Line 15 here, those results are not materialized and they're actually re-queried every single time if you're referencing those tables more than once on one of your long queries. It's a trade-off between readability for your code and also performance or speed when you're storing those tables as permanent. Here's another example that demonstrates that same concept. For example, if you're only interested in looking at the charities that are schools in the IRS 2015 dataset and you're commonly passing this filter over and over again, it's better to actually store those results as a permanent table and then query those directly. Now let's revisit our friend, the GROUP BY clause. If you remember the GROUP BY actually aggregates those values over the subset of data that you have. If you're performing, in this particular case, we're looking at the number of Wikipedia contributors and then grouping the amount of edits that they made on the amount of IDs or editors that there are for that Wikipedia dataset. If you can imagine, there's a ton of different contributors and doing such a large GROUP BY potentially creates many, what we call forced shuffles or forced communication steps between your underlying slots or workers. One of the ways you could get around this is potentially performing another level or high level aggregation on your dimension on the contributor here to bucket them so you have fewer buckets that you're grouping by. We'll look at this example in greater depth in just a few slides. Moving on to the computational aspect of BigQuery. If you wanted to optimize the CPU usage and you're getting resources exceeded errors, take a look at any JavaScript user-defined functions you might have and see if there's any way to revisit those concepts within native SQL functions or potentially SQL user-defined functions. The reason being here is that BigQuery actually has to fire up a Java subprocess to run a lot of your JavaScript code, but that doesn't mean you shouldn't use UDFs. The UDFs have their place when there's functions that are just outside of the normal scope of SQL capabilities, but just keep in mind the performance trade-off there. Now, as we discussed a lot in the joins and unions lecture, keep in mind that you want it fundamentally master your data model on how your data tables are related to each other. The relationship between customers and orders, one customer can have many orders, or one order can only belong to one customer, going through that mental exercise like what we did with the weather stations and the temperature data will help you determine whether or not the results sets that you get makes sense. Now, one of the specific examples that we looked at earlier was uncovering the insight that one particular charity filed more than once for the tax year 2015 and doing something like a sum of all revenue across all those rows would bring in data that was potentially duplicative across multiple charities. Keep in mind, understanding that data model or working with your subject matter experts to determine what those unique row conditions or fields should be is critical. Next, if you're combining data historically, be sure to use that table suffix and be as granular as possible so you can avoid reading more data than you intend to use. Lastly, if you've used SQL a bunch before, you might have run into self joins, consider using those window functions instead for optimal performance. Another great performance tip is to keep intensive operations like ORDER BYs to be the very last thing that you're doing in your queries. For example, here as you can see, we have a named subquery there. With Line 12 we're actually ordering it, but we actually don't need to use an ORDER BY there because we're not doing anything with ordered results. One of the easiest ways to spot this new query is if you're doing more than one ORDER BY. If you have an ORDER BY like you see here in the named subquery on Line 12, since we're not actually doing anything with the order of that data there, we can actually remove that ORDER BY all the way to the end if we wanted to do something like order by charity names. This allows you to take the advantage of any filtering that's going to happen before, so we're only ordering by those results that actually matter to us in the very end.
