1

When you want to encapsulate your T-SQL to select different subsets of data from the same base tables, is it more efficient to use plain views in conjunction with nonclustered indexes and INCLUDEs on the base tables, or is it better to use multiple indexed views, even when writes are frequent?

Background: I've run into a design issue that could cause me a lot of problems down the line if I handle it incorrectly, so I'd like some feedback on how best to approach it. Essentially, I have a series of tables consisting mainly of float columns which I need to join in hundreds of queries, each of which retrieves myriad subsets of the same columns and joins them together in similar but not always identical ways. For ease of maintenance, code legibility, modularization and the like, I'd to encapsulate as much of the commonalities in T-SQL across the queries as possible. For example, in the sample code below, I select a slightly different list of columns in the second query than in the first, plus join to a third table; there are dozens of such permutations of similar SQL statements scattered across hundreds of queries. Most of the queries occur in stored procedures that perform one or more UPDATEs, plus some rare DELETEs or INSERTs.

Stored Procedure Example 1

; WITH CTE1
AS
(SELECT T1.ID, Column1, Column2, Column3, Column4, Column3InTable2, Column5InTable3
FROM Table1 AS T1
    INNER JOIN Table2 AS T2
    ON T1.ID = T2.ForeignKeyID
)

UPDATE T1
SET Column1 = Whatever
FROM Table1 AS T1
    INNER JOIN CTE1 AS T1
    ON T1.ID = T2.ID

Stored Procedure Example 2

; WITH CTE1
AS
(
SELECT T1.ID, Column1, Column2, Column3, Column5, Column3InTable2, Column2InTable3, Column3InTable3
FROM Table1 AS T1
    INNER JOIN Table2 AS T2
    ON T1.ID = T2.ForeignKeyID
        INNER JOIN Table3 AS T3
        ON T1.ID = T3.ForeignKeyID
)

UPDATE T1
SET Column3InTable3 = Whatever
FROM Table1 AS T1
    INNER JOIN CTE1 AS T1
    ON T1.ID = T2.ID

What I'd like to use are simplified retrieval structures like this:

CREATE VIEW View1
AS
SELECT T1.ID, Column1, Column2, Column3, Column4, Column3InTable2, Column5InTable3
FROM Table1 AS T1
    INNER JOIN Table2 AS T2
    ON T1.ID = T2.ForeignKeyID

CREATE VIEW View2
AS
SELECT T1.ID, Column1, Column2, Column3, Column5, Column3InTable2, Column2InTable3, Column3InTable3
FROM Table1 AS T1
    INNER JOIN Table2 AS T2
    ON T1.ID = T2.ForeignKeyID
        INNER JOIN Table3 AS T3
        ON T1.ID = T3.ForeignKeyID

Stored Procedure Example 1 Updated

UPDATE T1
SET Column1 = Whatever
FROM View1

Stored Procedure Example 2 Updated

UPDATE View2
SET Column3InTable3 = Whatever

From experience, I've already learned that retrieving the data through table-valued functions leads to poor performance, which improved dramatically when I created a single indexed temporary table on all of the combinations of columns these queries need. Unfortunately, creating different temp tables that retrieve only the subsets of data I need for each query quickly turns into a maintenance and coordination nightmare. Therefore, I still need to refer to complicated joins to the same broad temp table in every procedure, which doesn't help me modularize things at all. Ideally, I'd like to use views to encapsulate the code (look at how much easier it is to read the samples above when they refer to views), but I imagine that creating different indexed views for each of the dozens of base queries I need would rapidly degrade performance, since all of the base tables are frequently updated on almost a 1:1 basis for each read. Could I get around this by instead using a series of non-indexed views to operate only on the columns I need, while indexing only the base tables with a series of INCLUDE clauses tailored to each subset of columns, or am I doomed to run into the same performance degradation due to the frequent UPDATEs?

Thankfully, I only need to update one base table in any given statement, so the restriction against updating multiple tables in a single view isn't an issue (typically, I only need to retrieve the other tables in order to calculate the new values of the updated columns). It's mainly the frequency of the updates which is complicating my efforts at encapsulation. After reading these Microsoft articles on Designing Indexed Views and indexing as well as the replies in the thread Using indexed views for aggregates - too good to be true? I haven't yet seen anything that would discourage me from using this approach; perhaps there's a better one I haven't thought of yet though. I've also toyed with the idea of building these views upon each other hierarchically to save even more code, but don't know if that would further complicate things. Thanks in advance for any advice.

  • Have you tried an inline table-valued function? It does not have the performance issue that a MSTVF has. More info: blog.waynesheffield.com/wayne/archive/2012/02/… – Tara Kizer Aug 23 '16 at 3:29
  • Yes, actually the performance was several order of magnitude slower than with an indexed temp table. After I switched to a temp table that retrieved the same structure as the TVF I ended up with zillions of missing index warnings; after I added a few of them, the performance improved dramatically. I suppose I ought to go back and check the execution plan for the TVF and see if I can see anything that can be optimized (I'll report back later). Thanks. – SQLServerSteve Aug 23 '16 at 4:09
  • @Tara Actually I was mistaken, my original TVF was a multi-statement function, not inline. I'll see if I can optimize that based on the post by Sheffield that you cited. If it works, I'll write one for each unique subset of columns/tables I need. Thanks. – SQLServerSteve Aug 23 '16 at 4:12
2

If your previous multi-statement table-valued functions (MSTVF) solved your problem but at the cost of performance, then you should rewrite those functions to be inline table-valued functions (ITVF).

MSTVFs execute once for EVERY ROW. Switching to an inline table-valued function and then CROSS APPLYing that function to your query will mean the function is only run once. You should see significant performance improvement with this approach.

See Wayne Sheffield's Comparing Inline and Multi-Statement Table-Valued Functions post for more information.

  • +1 for effort but I now realize views would be preferable to TVFs. Although I did see performance increases by switching to inlines, I still ran into 2 inescapable limitations. First, TVFs aren't updatable, which means I end up having to resort to awkward joins to the base tables to run my UPDATEs. Second, I sometimes have to run more than one UPDATE against the same query in a particular procedure. With a TVF, I'd have to retrieve the data each time, thereby multiplying the retrieval cost further for each UPDATE (or other operation). Encapsulation and possibly performance are thus reduced. – SQLServerSteve Aug 23 '16 at 17:03
  • 1
    @SQLServerSteve: Re: TVFs aren't updatable – stackoverflow.com/questions/16568454/…; re: more than one UPDATE against the same query – not sure what you mean by that. I don't think "retrieving" data with an iTVF would be any different from "retrieving" data with a view. An inline TVF is essentially just a parametrised view. – Andriy M Aug 24 '16 at 12:29
  • @Andriy I've been using TVFs for years and never had the faintest clue that they were updatable. I'll check this out and make sure it meets my needs for this specific problem & report back - but even if it doesn't, this info will still come in handy for years to come. Thanks - it never ceases to amaze me that just when you think you know T-SQL well, some feature like this jumps out and surprises you. This will turn out to be useful to me for years to come. – SQLServerSteve Aug 24 '16 at 17:34
  • This really turned out to be the perfect solution for the problems I'm trying to solve - thank you. I've been able to drastically consolidate the structure of the solution and modularize all the code, plus get all the benefits of index tuning. I even found out that it's possible to build such inline TVFs hierarchically upon one another without losing the ability to UPDATE and with no visible performance impact yet. It's also possible to stack them with CROSS APPLY's in a single query and still use UPDATE. – SQLServerSteve Aug 26 '16 at 3:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.