The Importance of Simple Query Optimization

After my customary, inexplicable year off from posting, I decided that the best way to get back on the horse is with a quick note about how really minor tweaks to SQL queries can make a huge difference in performance. This issue came up while I was doing some preliminary tinkering with a potential recommendation engine-style project, and I thought that it was a really good example, especially for people who are new to SQL or are starting to do more complex queries against relational databases, but haven't had to worry about execution time too much yet, either because the database is small, or it isn't code that will run in production, or what have you.

Below you will see screenshots of two nearly identical queries, which serve to basically just identify the most common video series viewed by users who have watched one other series. You could simplify the concept by saying, 'Users who watched X also watched Y' (and we only want the top five results).

Version 1:

Version 2:

Now, if you look in the lower right corner of each of these, you will see how long the query took to execute, and notice immediately that the Version 1 query took 5x as long as the Version 2 query.

(Note: 105 seconds is brutal, but 21 seconds is still too slow for most applications, but we are focused on relative difference for now. This was querying a PostgreSQL database, but running the same query against an Amazon Redshift db took less than 5 seconds!)

The results are the same for both, so what's the difference here? If you look at the middle sub-query, aliased here as '_other_views,' you will notice that in Version 2, there is a GROUP BY clause that is missing from the first version. Why does it make it so much faster? Think about what this query is trying to accomplish by segment.

First segment - identify all users who watched series with an ID of '1688' since [date]

Second segment - of those users, identify each OTHER series that every one of them has watched since [date]

Third segment - get a count of how many users have watched each other series, order them from most viewers to fewest, and take the top five

That second segment is the tough one. Looking at a longer time period, with potentially thousands of users watching thousands of series dozens of times each, we are still performing a search over millions of rows in the database. In the first query version, however, for each instance of an individual watching a series we are creating a row in the temporary table _other_views, but the reality is that we only care about the first instance of viewing each series (this is also a place we could consider using an 'EXISTS' statement).

Instead of :
Person 1 :: Series 1 [:: View 1]
Person 1 :: Series 1 [:: View 2]
Person 1 :: Series 1 [:: View 3]
Person 1 :: Series 2 [:: View 1]
Person 1 :: Series 2 [:: View 2]
etc.,

We just want:
Person 1 :: Series 1 [:: View 1]
Person 1 :: Series 2 [:: View 1]
etc.

And that's what the GROUP BY in that sub-query achieves. It makes sure that we don't create duplicate rows of each combination of User/Series in our temp table, that the final SELECT statement then has to churn through in order to get the count of distinct viewers for each series.

Another way to demonstrate the difference is to only run the first two sub-queries, in order to see how big of a result set your final SELECT is forced to sift through:

Version 1 (without GROUP BY) - 3,628,502 records returned

Version 2 (with GROUP BY) - 11,903 records returned

So just that one little change in our middle sub-query results in an immense dimension reduction of the result set, with over 300x fewer records to count and order by in the final step. Both ways are technically 'correct' in that they will execute fully and return the same output, but it's a great example about how a little attention to what's actually going on under the hood can pay huge dividends in performance.

Own Your Data (Or at least house a copy)

There is a common trope among data analysts that 80% of your time is spent collecting, cleaning, and organizing your data, and just 20% is spent on analyzing or modeling. While the exact numbers may vary, if you work with data much you have probably heard something like that, and found it to be more or less true, if exaggerated. As society has moved to collecting exponentially more information, we have of course seen proliferation in the types, formats, and structures of the information, and thus the technologies that we use to store it. Moreover, very often you want to build a model that incorporates data that someone else is holding, according to their own methods that may or may not mesh well with your own.

For something as seemingly simple as a marketing channel attribution model, you might be looking at starting with a flat file containing upwards of 50-100 variables to start, pulled from 10+ sources. I recently went through this process to update two years' worth of weekly data, and it no joke took days and days of data prep just to get a CSV that could be imported into R or SAS for the actual analysis and modeling steps. Facebook, Twitter, Adwords, Bing, LinkedIn, YouTube, Google Analytics, a whole host of display providers... the list goes on and on. All of them using different date formats, calling similar/identical variables by different names, limited data exports to 30 days or 90 days at a time, etc..

Obviously, worth the effort for a big one-time study, but what about actually building a model for production? What about wanting to update your data set periodically and make sure the coefficients haven't changed too much? When dealing with in-house data (customer behavior, revenue forecasting, lead scoring, etc.) we often get spoiled by our databases, because we can just bang out a SQL query to return whatever information we want, in whatever shape we want. Plus, most tools like Tableau or R will plug right into a database, so you don't even have to transfer files manually.

At day's end, it quickly became apparent to me that having elements of our data, from CRM to Social to Advertising, live in an environment that I can't query or code against is just not compatible with solving the kinds of problems we want to solve. So of course, the next call I made was to our superstar data pipeline architect, an all-around genius who was building services for the Dev team running off our many AWS instances. I ask him to start thinking about how we should implement a data warehouse and connections to all of these sources if I can hook up the APIs, and he of course says he has already not only thought of it, but started building it. Turns out, he had a Redshift database up and was running Hadoop MapReduce jobs to populate it from our internal MongoDB!

So with that box checked, I started listing out the API calls we would want and all of the fields we should pull in, figure out the hook ups to the third party access points. Of course, as we have an agency partner for a lot of our paid media, that became the biggest remaining road block to my data heaven. I schedule a call to the rep in charge of their display & programmatic trade desk unit, just so we can chat about the best way to hook up and siphon off all of our daily ad traffic data from their proprietary system. After some back and forth, we finally arrive at a mainly satisfying strategy (with a few gaps due to how they calculate costs and potentially exposing other clients' data to us), but here is the kicker:

As we are trying to figure this out, he says that we are the first client to even ask about this.

I was so worried about being late to the game, that it didn't even occur to me that we would have to blaze this trail for them.

The takeaway? In an age of virtually limitless cheap cloud storage, and DevOps tools to automate API calls and database jobs, there is no reason that data analysts shouldn't have consistent access to a large, refreshing data lake (pun fully intended). The old model created a problem where we spend too much time gathering and pre-processing data, but the same technological advances that threaten to compound the problem can also solve it. JSON, SQL, Unstructured, and every other kind of data can live together, extracted, blended and loaded by HDFS into a temporary cloud instance, as needed.

The old 80/20 time model existed, and exists, because doing the right thing is harder, and takes more up-front work, but I'm pretty excited to take this journey and see how much time it saves over the long run.

(Famous last words before a 6 month project that ultimately fails to deliver on expectations; hope springs eternal)

What do you think? Have you tried to pull outside data into your own warehouse structure? Already solved this issue, or run into problems along the way? Share your experience in the comments!