Best Practices for Faster Query Speed
November 29th, 2016 Dylan Gessner
At Metamarkets, we have designed our product to provide data-rich answers to our users’ questions as quickly as possible. Our original hypothesis was that if we put programmatic data at the fingertips of our users, they would get the insights they need and then leave the product to continue on with their other daily tasks. However, over time we have observed just the opposite– by providing quick, data rich answers to our users questions, those users actually stay in product longer, often times, throughout the whole day.
With most Metamarkets users running multiple queries throughout the day, we wanted to share four best practices for interacting with your data to ensure a fast and enjoyable user experience:
1. Minimize Unused Fields
Before you start looking for answers to a particular question, minimize all dimension tables and hide all fields that are not of interest to the particular query you are about to run. Reducing the number of dimension tables and fields reduces the number of outbound queries needed, which means faster load times for the data you’re interested in.
Pro-Tip: Before you start a new query, be sure to take an extra minute to clean up after your last query by minimizing all unneeded tables and fields. If you don’t want to lose the settings from your previous query, simply bookmark the view (see below for details!)
2. Filter First
If you know what your specifically looking for, filtering your data before changing any time period settings or adding any data fields will trigger a more targeted query for a smaller subset of data. This will result in faster load times if you then decide to expand a dimension table and/or lengthen the time period of interest. For example, if you are interested in what ad sizes are performing well within the United States for Android devices, filtering for “United States” and “Android” first will only query for that small subset of data once you start pulling up fields and expanding date ranges.
Pro-Tip: When searching for a value on which to filter, use the search bar to find the specific dimension for faster search results.
3. Zooming Date Ranges
Taking advantage of zooming when analyzing large periods of historical data helps when, for example, you want to pull a quarter’s worth of data for an upcoming QBR. By zooming out of your data sets you can break a 3-month long query into smaller increments to get the desired results faster.
Start from the initial load state and zoom out to successively larger time periods. This breaks the query into smaller increments and caches the data as you go along. Once you have zoomed out to 30 days, use the arrows to toggle back one month at a time. Make sure each month loads before toggling back to the next.
Next, open the date selector and select the full three-month range. The data will load quickly, and you can open the dimension table of choice.
Pro-Tip: These queries will run even faster if you minimizes unnecessary dimension tables and metrics and have already applied the filter set.
4. Bookmark Views
With the recent updates to Explore, you are now able to Bookmark specific dashboard views. That means you can save common use cases instead of building them each time you log in. For example, if you gets asked the same question by many DSP or Publisher clients, you should create a Bookmark with the relevant dimensions and metrics shown to answer that question. Then, you can drill down for the specific partner and take advantage of the cache for faster initial load times.
Pro-Tip: Use the best practices of Filtering First and Zooming within a Bookmarked view to get even faster answers.
Dylan Gessner is a Technical Account Manager at Metamarkets. As a product expert, he is responsible for training end users on how best to use the Metamarkets dashboard.