One of the things I do at my day job (Cloud Database Administrator) is troubleshoot issues with a Business Intelligence tool that is a GUI based, drag-and-drop interface. This type of tool is fabulous for business users without a ton of experience in direct SQL, but at the same time, can really get them into a pinch when they build a report that doesn't translate into a performant, well architected query. The same can be said for even the most seasoned Business Intelligence professional. Without the ability to directly manipulate the query the user relies on the software to build what they intend, and that isn't always the case.
I ran into a situation like this recently. One of our resident business intelligence developers built a report to client spec with a good deal of calculations, CASE WHEN statements for data identification, and joins between large tables. Even on the best of days, the database might not be using the index that you want, and with a UI based tool, there are no index hints.
The query produced by the report was four subqueries joined together based on a data point that identifies a type of information, and the data span was one quarter. What this data is doesn't really matter as what I want to point out here are the basic steps that I take to identify issues. Perhaps get you thinking outside of the UI-box for answers.
The first thing I do is take the SQL to a database editor and run it. Are you getting the same run times as you do in the product? Yes, great. No, then you might be dealing with a rendering or connectivity slowdown. This is likely something that will need to be taken to an administrator to work on.
The next step is to break down the query into manageable chunks. If you have subqueries, run each of them separately as well as run an explain plan on each. Try to identify if one subquery is the issue. Did you forget to add a filter to the subquery or forget to join a filter back to the master query? Is one subquery not using an index or not using the index you intended? Once you find the problem section, focus on that. Strip the query down to basics: If there are a ton of calculations in the SELECT clause try removing those and slimming down to a single ID. If your time improves greatly you probably have a calculation that needs rethought. If it doesn't, focus in on your joins. Is there a table that is not indexed properly? You can identify this via the explain plan. Although deciphering the explain plan is not an exact science, you can often see things like 'using temporary; using filesort' or an empty key column that might lead you to a poorly indexed table or a better way to write your SQL.
If you are still stuck at this point, it might just be the amount of data produced by the report and the processing power you have on your system. Here I think it is safe to take your concerns to the administrator, but be sure to tell them about your due diligence in exploring the report. That information might give them some ideas about how they can help you improve your reports performance.
The biggest thing I would want you to take away from this is not to panic, don't only work with one tool (your GUI BI tool), use every option available to you. I promise you that it will be much easier to debug any SQL based reporting by looking at the actual SQL, where you can freely manipulate it, against the database. There are guaranteed to be far more debug tools on the RDBMS that there are in the GUI tool.
I primarily administer a MySQL system, but also work with PostgreSQL and Redshift, and am learning about Snowflake (which is pretty dang awesome by the way!). Each of these tasks can be different on different systems so be sure to look up the equivalent for your database, and remember, the thrill is in the hunt. To quote one of my favorite movies, The Goonies, "Come on, guys, this is our time. Our last chance to see if there really is any rich stuff. We’ve got to." Working with data is like a grown-up treasure hunt. You might not always find what you were looking for, but when you do, you can save the Goondocks.
Comments