Lessons Learned from a Poor-performing VIEW

Posted on Updated on

It is a “common knowledge” among SQL practitioners that VIEWs don’t perform well. Recently during an application’s development I put this to the test. I needed to construct a VIEW for a report the system was producing. It was highly complex. Let’s take a look at the general structure of the VIEW that I ended up with, along with how it was called by the report.

CREATE VIEW dbo.v_VeryComplex
WITH SCHEMABINDING
AS
WITH aCTE AS
(
    SELECT something
    FROM dbo.a_table a
    JOIN dbo.b_table b ON a.key = b.key
)
SELECT col1, col2, col3
-- many columns including a few calculated ones
FROM
(
    SELECT col, col2, col3-  - plus many more
    FROM dbo.table1 a
    JOIN dbo.table2 b ON a.key = b.key
    -- plus about 14 more JOINs and CROSS APPLYs
    -- among them the aCTE defined above and at 
    -- least one call to an schema-bound, in-line
    -- table valued function
    WHERE a.col = 'abc'
) a;
GO
-- The report calls it thusly 
SELECT col1, col2, col3 -- plus many more
FROM v_VeryComplex
WHERE col2 = 'cde' AND col3 = 'def'
ORDER BY sortcol1, sortcol2;

When I checked the Execution Plan for calling this VIEW (as the report does) during development, I was quite happy to see that every table it hit did an INDEX SEEK. So all of my JOINs, etc. were in order and it looked like it would happily perform with swiftness. And while I was on-site for the Go Live of the application, it most certainly performed with alacrity.

Then about four months later comes the dreaded call from the user. My beloved VIEW with all of those SEEKs was taking 30-40 seconds to return its results.

My first reaction was to be incredulous of course. Perhaps the report rendering to the browser was taking a long time. So I set about to testing it. I captured a query from the report using SQL Profiler and then ran it on the production server, where by this time some 12,000+ transactions had been entered into Table2 (from the VIEW above). Table1 is a configuration table and is quite small.

Sure enough it was dog-slow, returning in about 40 seconds. Not good.

A quick check of the execution plan on the production server showed that I was now suddenly getting an INDEX SCAN on Table2, which means that the VIEW was performing all of its work on all of the transactions in that table including a whack of complex calculations, summaries of detail records in child tables, etc. The final filtering on the VIEW then was not being transferred into an INDEX SEEK like it used to be!

A little more about the application so you can better appreciate my position. First of all, the application itself is mission critical for this customer. It has to work, and all of the work it processes is quite time sensitive. The report itself isn’t used for every transaction in the table, in fact probably less than 20-25% use this particular report to be processed. Nonetheless, those transactions are just as time-sensitive as all the rest. And because it involves some governmental reporting, it must be 100% accurate. Inaccuracies in the calculated amounts could lead to fines to the customer.

With more than a little trepidation, I set about to rewriting the VIEW in the hopes that I could remove the outer derived table (a) to perhaps return my call to the VIEW to doing an INDEX SEEK on Table2. Fortunately, I had at my disposal a local copy of an older version of the production database that exhibited the same problem – an INDEX SCAN on Table2.

After not a large amount of effort, I was able to do that rewrite and get my INDEX SEEK! So I happily created the new VIEW in the production server to continue my testing to ensure the accuracy of my results. The way I did that was to run the original VIEW, followed by EXCEPT, followed by the results from the new VIEW. Since I was running both VIEWS against all rows in Table2, this should highlight any discrepancies (after checking first that both returned the same number of rows). The result of that test were no rows returned, establishing with a very high degree of certainty that my rewrite was producing the correct results. I was nearly beside myself with glee!

Alas, that emotional state was short-lived because when I checked the execution plan of the call to the new VIEW in production, I found that I was back to the INDEX SCAN on Table2.

In fact, when I modified the filtering criteria to return the single transaction it was intended to return, I got virtually the same execution time. Not good!

What’s a performance evangelist like me to do? I certainly couldn’t go back to the client and tell him there’s nothing that can be done.

So I did a quick re-evaluation of the situation. My first thought was “perhaps I can INDEX the VIEW.”

That was not an option because:

  • You can’t INDEX a VIEW that uses common table expressions (CTE).
  • While I could modify the VIEW and move that CTE into a derived table, I was still left with several CROSS APPLYs, and you can’t INDEX a VIEW that uses APPLY either!

Rewriting all of those CROSS APPLYs into JOINs was simply not an appealing prospect to me.

Another rather annoying aspect of INDEXing the VIEW, were I able to accomplish it, is that the VIEW is schema-bound and it calls that pesky schema-bound, in-line table valued function (iTVF) mentioned in the comments in the code shown above. There is already a specific hierarchy to the deployment when one of the lowest level schema-bound objects needs changing (like that iTVF) that is relatively a pain. It involves dropping the VIEW and re-creating it after altering the iTVF. If I were to INDEX the VIEW, I’d always need to remember to recreate that INDEX anytime I had to deploy anything in that hierarchy. Not good either.

Then it hit me like a 2×4 to the head! When the report calls my VIEW, it does it for a single transaction only. So that gave me the solution.

I could copy all of the original code from the VIEW and instead create a schema-bound, ITVF. That would look something like this.

CREATE FUNCTION dbo.VeryComplexFcn
(
    @parm1      VARCHAR(10)
    ,@parm2     VARCHAR(10)
)
RETURNS TABLE WITH SCHEMABINDING
RETURN
WITH aCTE AS
(
    SELECT something
    FROM dbo.a_table a
    JOIN dbo.b_table b ON a.key = b.key
)
SELECT col1, col2, col3
-- many columns including a few calculated ones
FROM
(
    SELECT col, col2, col3  -- plus many more
    FROM dbo.table1 a
    JOIN dbo.table2 b ON a.key = b.key
    -- plus about 14 more JOINs and CROSS APPLYs
    -- among them the aCTE defined above and at 
    -- least one call to an schema-bound, in-line
    -- table valued function
    WHERE a.col = 'abc' AND a.col2 = @parm1 AND b.col3 = @parm2
) a;
GO
-- The report calls the iTVF like this 
SELECT col1, col2, col3 -- plus many more
FROM dbo.VeryComplexFcn ('cde', 'def')
ORDER BY sortcol1, sortcol2;

It was in short order that I deployed this new iTVF to the production server and checked the execution plan, to find that I was back to my beloved INDEX SEEK!

The query elapsed time calling the iTVF instead of the VIEW for a single transaction was impressively improved.

New (iTVF):
 SQL Server Execution Times:
 CPU time = 62 ms, elapsed time = 64 ms.
Old (VIEW):
 SQL Server Execution Times:
 CPU time = 35194 ms, elapsed time = 44423 ms.

I’m keeping the VIEW around for posterity’s sake, and of course in the event that we ever need to run it for multiple transactions.

Now I’ve just got to wait for the development team to modify the report to call the iTVF instead of the VIEW, so we can re-deploy the application. I am left with the same hierarchical deployment constraints, but those I can live with because I won’t need to remember to recreate an INDEX on a VIEW every time (SQL Server will remind me about everything else).

The lesson to be learned here is not to be beguiled by execution plans you see during development. Those can change as transactions accumulate. Even though we generated an impressive number of transactions during the development (I had some automated SQL scripts that did this), they were insufficient to reach that critical threshold where the execution plan changed.

Advertisements

9 thoughts on “Lessons Learned from a Poor-performing VIEW

    Thomas Franz said:
    March 17, 2015 at 3:15 pm

    Did you check, if the CTE was executed before or after the filtering / joins in the main query?
    Sometimes the SQL-Server executes the CTE for all rows in a large table, even if it has to do it only once – when it would do the other joins first.

    In such cases it would help to add a dummy UNION to the CTE which forces the SQL Server to materialize the CTE before joining it to the rest -> performance boost.

    Example:
    WITH aCTE AS
    (
    SELECT something, something2 — plus x more columns
    FROM dbo.a_table a
    JOIN dbo.b_table b ON a.key = b.key
    union
    select null as something, null as something2 — plus x more nulls
    )

    Caution: if you use only LEFT JOIN’s to the the CTE you have to add a “aCTE.something is not null” to the JOIN condition.

      Dwain Camps responded:
      March 18, 2015 at 2:15 pm

      While I did not try what you suggest, it did not appear to be the problem. The index scan that mysteriously appeared was the root cause. I have hopefully eliminated it forever, but only time will tell.

    rsterbal said:
    March 20, 2015 at 8:39 pm

    Could you wrap the function into the view (and not have to change anyone’s code?)

      Dwain Camps responded:
      March 25, 2015 at 9:42 am

      The problem with that is that when calling the VIEW you need to specify limiting criteria. And that can’t be passed into the VIEW.

      At least I think that’s the case.

    g003p3k said:
    May 20, 2015 at 7:47 pm

    Could you post the code for your your modified view that removed the subquery and still failed in production? This is an essential part to your post. I’m really curious to see what it looks like. If you can include the SqlSentry anonymized version that would great as the execution plan might also give us a picture of what was going on.
    Thanks!
    sqlbarbarian.com

    g003pk said:
    May 20, 2015 at 7:51 pm

    An essential piece is missing in this article, the code created to remove the subquery on the view! I’m really interested in seeing this. Could you update your post with this intermediate step that didn’t resolve your issue?

    I’d also suggest including a screenshot of the execution plan from SQL Sentry Plan Explorer. It has an anonymize function, that can take your entire query and execution plan and remove table/object names automatically. I’m really interested in seeing the result.

      Dwain Camps responded:
      May 21, 2015 at 9:58 am

      I had to go back and read through the article to see if I could identify the subquery you’re referring to because I couldn’t remember anything about it. Unfortunately, I still don’t know what you are referring to. Could you pinpoint which paragraph I mention it in?

      I do not use either of the two tools you referenced, but I’d be happy to see if I can post additional information to clarify. I know the entire query in the VIEW is way to huge to post (I wasn’t exaggerating when I said the 16+ tables it hits).

      After reading through again, I realized at the end I sort of left my readers hanging on the point “waiting for the development team to use the function instead of the VIEW.” Well, let me note that now. The feedback I got from the client after deploying this to production was that the user “was impressed with the speed of the new report.” That is feedback I’ll happily accept, and frankly quite made my day.

        g003pk said:
        May 21, 2015 at 6:50 pm

        Thanks for your quick followup.

        The section I’m referring to says:

        >With more than a little trepidation, I set about to rewriting the VIEW in the hopes that I could remove the outer derived table (a) to perhaps return my call to the VIEW to doing an INDEX SEEK on Table2. Fortunately, I had at my disposal a local copy of an older version of the production database that exhibited the same problem – an INDEX SCAN on Table2. After not a large amount of effort, I was able to do that rewrite and get my INDEX SEEK!

        The conclusion of a inline table function providing the speed boost makes sense with the subquery structure from the first sample. However, if rewritten to not have a subquery, I’m having trouble identifying why the execution plan would be dramatically different. An inline table function should merely be a wrapper for the logic, so if truly exactly the same excepting one is a view and one is an inline table function… well they should execute the same.

        Sql Sentry is a great tool. It has a function to anonymize the entire query (even a huge one) and post to their Q&A for performance. I’d suggest you leverage that, as it protects privacy, but also ensures the entire sql execution plan picture is being provided for readers. I don’t know if you can attach a file in wordpress, but at least posting it up for performance evaluation on sqlperformance.com might get you some additional insight from some SQL MVP’s, and help us as readers.

        Thanks again! I look forward to reading in more detail.

    […] a way that the SQL Optimizer simply starts making bad choices for choosing an execution plan (see: Lessons Learned from a Poor-performing View).  Maybe the darned thing is very, very complex and you’re pushing up against tight delivery […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s