We have the case where in a reporting solution for an application it was decided to build a series of reporting views, fair enough. These views are layered, nested several levels deep at times and, not unexpectedly, as our customer's data grows so these
To complicate matters further, the report SQL may then join several of these top level reporting views to produce the data it needs.
In a recent support request for a slow performing report we ripped the sql out of the report, pasted it into SSMS and while investigating found a join error in the reports SQL that took execution time from ~ 2 minutes down to about 6 seconds... Regardless
of what we did after that we were unable to reduce the time below 6 seconds using our reporting views. Rewriting the SQL against our base tables takes a fraction of a second to return the needed information.
What I need to do is demonstrate where the extra time in getting the results from the views is spent. My guess is that it is in untangling all the views and then ending up with a less than optimal query plan. Is there anyway to measure how long it
takes the server to "unpack" the nested views into a single SQL statement before it can be executed? Knowing how much of the 6 seconds this is accounting for would be helpful to us in our decision making process.
View Complete Post