A general rule with BI reports and dashboards is 10 seconds or less for a report or 30 seconds for dashboards.
But quite often an analyst will run a report and it never comes back.
They’ll say something like so and so report is running slow or broken.
Here’s some ways to troubleshoot this.
1. You’re pulling too much data from the table and it’s not cached.
Try adding a view filter or report filter. Different BI apps have different terms.
Edit the report so it does a where statement against the data-source.
Otherwise, if there’s a cube/cache process for this set of reports then by all means cache it.
2. There’s a messed join somewhere. This is likely case with MicroStrategy. Managing any BI tool’s sql generation is always a hit or miss. Sometimes it the BI architect messed up the schema relationships. Sometimes the Looker explore wasn’t meant to cross 5 objects. Accidental many to many joins can be caused by the report’s sql generator. If it never comes back; this is a possible reason.
3. Someone wrote some bad custom sql job that ‘works’ but doesn’t really work well. You’re going to have to pull up your sleeves and dig into the details. I hope you’re not the unlucky sob to taking over from some previous consultant’s Cognos project that was out-sourced to India and being held together with bubble gum and duct tape.
Tips to fix SQL performance:
-never join on text/string/varchar fields between two tables, if you do index it.
-index join columns and try to keep join columns always as integers.
-remove complexity like multiple levels of sql subqueries and other craziness.
-Use optimized CTE’s which are just fast temp tables. (Sql Server only case).
-Never ever use cursors for reporting.