locked
Nested Views execution time, how long does it take to "unpack" the SQL? RRS feed

  • Question

  • 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 views slow...

    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.

    Thanks,

    Bill

    Thursday, September 30, 2010 3:36 PM

Answers

  • Thanks for the replies, just some quick updates...

    With regards to the shredding of the SQL only being done once, that unfortunately doesn't seem to be the case. We are consistently seeing the same performance issue even when the same query is run repeatedly without changing parameter values.

    The query plan is indeed suboptimal, but even a table scan of a table with ~100 rows in it should complete in less than 6 seconds... 

    I did finally find what I was looking for... I found this blog entry http://sqlserverplanet.com/sql-optimization/using-set-statistics-time-on/ which is exactly what I needed. Using this it shows that the query is chewing up 4+ seconds of CPU time alone (See output below)

    The statistics time easily allows me to demonstrate why it is take 6 seconds to return three records and was a real eye opener for the team...

     When selecting from our view we get this (ouch)...

     SQL Server Execution Times:

    CPU time = 4212 ms, elapsed time = 6752 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    (3 row(s) affected)

    (1 row(s) affected)

     When Selecting from the table with a where clause we get this

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 53 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    Thanks again for the replies,

    Bill

    • Marked as answer by Bill Lucas Friday, October 1, 2010 6:33 PM
    Friday, October 1, 2010 6:32 PM

All replies

  • Shredding of the views etc will happens before the stage of preparing execution plan only..

    Once it prepares execution plan, it will use the same execution plan for next executions... so there won't be shredding again..

     

    I believe, as there are multiple views, it might choosing a sub-optimal plan...

    Can you check the old query execution plan and your new query execution plans, and check whether they are generating same plan or different?????

    Thursday, September 30, 2010 4:21 PM
  • There is no doubt that abusing of multi-level views (nesting) could yield a sub-optimal plan.

    Can you post an example of a query referencing those views?

     


    AMB

    Some guidelines for posting questions...

    Thursday, September 30, 2010 5:27 PM
  • Thanks for the replies, just some quick updates...

    With regards to the shredding of the SQL only being done once, that unfortunately doesn't seem to be the case. We are consistently seeing the same performance issue even when the same query is run repeatedly without changing parameter values.

    The query plan is indeed suboptimal, but even a table scan of a table with ~100 rows in it should complete in less than 6 seconds... 

    I did finally find what I was looking for... I found this blog entry http://sqlserverplanet.com/sql-optimization/using-set-statistics-time-on/ which is exactly what I needed. Using this it shows that the query is chewing up 4+ seconds of CPU time alone (See output below)

    The statistics time easily allows me to demonstrate why it is take 6 seconds to return three records and was a real eye opener for the team...

     When selecting from our view we get this (ouch)...

     SQL Server Execution Times:

    CPU time = 4212 ms, elapsed time = 6752 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    (3 row(s) affected)

    (1 row(s) affected)

     When Selecting from the table with a where clause we get this

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 53 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    Thanks again for the replies,

    Bill

    • Marked as answer by Bill Lucas Friday, October 1, 2010 6:33 PM
    Friday, October 1, 2010 6:32 PM