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.