This is part 14 of a series. If you have not read part 13, you can find it here, but this article does not require any knowledge of the previous work in the series.

Translation: Using Views and “Instead of” Triggers

When everything in your application is created from scratch and everything is perfectly designed, the world is rather simple. But ... in normal cases, you have to make certain compromises.

SQL Server allows us to create a facade using views and triggers so that a table that originally had a certain design can be replaced by a view that looks like the original table to (almost*) any code that accesses it. The question I want to answer here is how this translation affects performance.

Building the Same-Database Facade

Creating a View

In previous installments of this series, I introduced the Card table, and I will use that as the basis for this testing. Here is the base Card record:

    CREATE TABLE dbo.Card (
        CardID         int          NOT NULL  IDENTITY,
        CardNumber1    bigint       NOT NULL,
        CardNumber2    bigint       NOT NULL,
        CardNumber3    bigint       NOT NULL,
        CardNumber4    bigint       NOT NULL,
        SecurityCode1  smallint     NOT NULL,
        SecurityCode2  smallint     NOT NULL,
        SecurityCode3  smallint     NOT NULL,
        SecurityCode4  smallint     NOT NULL,
        SecureString1  varchar(36)  NOT NULL,
        SecureString2  varchar(36)  NOT NULL,
        SecureString3  varchar(36)  NOT NULL,
        SecureString4  varchar(36)  NOT NULL,
  
        CONSTRAINT Card_PK PRIMARY KEY CLUSTERED (CardID)
    )

There are two indexes besides the PK: a unique index on CardNumber1/SecurityCode1 and a non-unique index on SecureString1.

For this same-database work, we will rename the Card table CardTable and create a view in the same database called Card. To focus on just the cost of the views and triggers, the view will exactly match the original Card table’s schema.

    CREATE VIEW dbo.Card
    AS
    SELECT
        [column list]
    FROM dbo.CardTable

Instead of Insert Trigger

Here we create a very simple “instead of insert” trigger; the values come from “Inserted”.

    CREATE TRIGGER dbo.trInsertCard ON dbo.Card
    INSTEAD OF INSERT
    AS BEGIN
        SET NOCOUNT ON
   
        INSERT INTO dbo.CardTable (
            -- CardID
            [non-identity column list]
        )
        SELECT
            [non-identity column list]
        FROM Inserted
  
        RETURN
    END

Then code that previously inserted into the Card table (“INSERT INTO dbo.Card ...”) will still work against the new view without changes.

Comparing Performance: Direct versus Same-Database Facade

Test Setup

The tests follow these steps:

• TRUNCATE TABLE CardTable
• Call CardCreateLoopN to insert 100,000 records directly into CardTable
• Call CardCreateLoopN to insert 100,000 records into Card (view)
• Call CardReadLoopN to read 100,000 random records from CardTable
• Call CardReadLoopN to read 100,000 random records from Card (view)

Results

I could post all the numbers and graphs, but the simple result is that reading through a view and inserting through a view and an instead of insert trigger does not add measurable overhead. In four different test runs, the results came out within small percentage-points (less than 4%) in duration. Sometimes one was faster, sometimes the other.

So, this shows that, at least in these tests, the cost of inserting into or selecting from a simple view over a table in the same database is negligible.

Building the Separate-Database Facade

If your changes require that you create a view in database A that is a facade for a table in database B, then things are more interesting. This is also more likely to be used in a real application. What we’re testing here is the pure overhead of translating from one database to the other. If the view actually pulled together multiple tables, then there would be extra cost.

To point out the realistic scenario here, I have called both the table and the view “Card”. It makes the code more difficult to follow, but it’s much closer to what this type of capability is used for. The value here is that you can create a view in one database that points to a table in another database so that the table can be changed while the code that interacts with the view does not have to change.

To help readability, the databases involved are called FacadeDB_table, FacadeDB_view, and FacadeDB_test.

Note that stored procedures CardCreate and CardReadByCardID exist in both databases and have the exact same code.

Creating a View

For this test, the Card table is in database FacadeDB_table and the Card view is in FacadeDB_view so that code that was previously written to access a table called Card can continue to work without changes.

Here is the new CREATE VIEW code:

    USE FacadeDB_view
    CREATE VIEW dbo.Card
    AS
    SELECT
        [column list]
    FROM FacadeDB_table.dbo.Card

Instead of Insert Trigger

This gets a bit more complicated. We have to create the trigger on the view, which is in FacadeDB_view. The trigger will insert the data into the base table, which is in FacadeDB_table.

    USE FacadeDB_view
    CREATE TRIGGER dbo.trInsertCard ON dbo.Card
    INSTEAD OF INSERT
    AS BEGIN
        SET NOCOUNT ON
   
        INSERT INTO FacadeDB_table.dbo.Card (
            -- CardID
            [non-identity column list]
        )
        SELECT
            [non-identity column list]
        FROM Inserted
  
        RETURN
    END

Code that previously inserted into the Card table (“INSERT INTO dbo.Card ...”) will still work against the new view without changes*.

Comparing Performance: Direct versus Separate-Database Facade

Test Setup

To keep things even since we’re calling stored procedures from T-SQL scripts, I created a FacadeDB_test database and called all the procedures from that database. That keeps me from calling local procedures for one test and procedures on a separate database for the other and skewing the results.

The tests follow these steps:

• TRUNCATE TABLE FacadeDB_table.dbo.Card
• Call CardCreateLoopN to insert 100,000 records directly into FacadeDB_table.dbo.Card
• Call CardCreateLoopN to insert 100,000 records into FacadeDB_view.dbo.Card (fires trigger in FacadeDB_table)
• Call CardReadLoopN to read 100,000 random records from FacadeDB_table.dbo.Card
• Call CardReadLoopN to read 100,000 random records from FacadeDB_view.dbo.Card

Results

Here are the results of four tests and a single example of the CPU usage graph. The integers (times) are in milliseconds.

The CPU graph shows four distinct “humps”. These correspond to the four steps listed above. I used WAITFOR to allow the CPU to drop to zero between steps.

First, it is interesting to see how consistent the “penalty” percentages are (especially on the read side). Second, this gives us evidence of the cost of the separate-database facade.

We know that the view and trigger do not affect performance when they are in the same database, so this penalty appears to be for spanning the gap between FacadeDB_table and FacadeDB_view.

All of the code for these tests is included, so if you would like to do something slightly different like put FacadeDB_table and FacadeDB_view on separate computers, great. If you do run tests like this, please share your results in the forum. Also, if you have comments or questions, please don’t hesitate to use the forum.

*Almost?

I wrote at the beginning of this article, “SQL Server allows us to create a facade using views and triggers so that a table that originally had a certain design can be replaced by a view that looks like the original table to (almost*) any code that accesses it.”

The one thing I haven’t been able to do with a view and an “instead of insert” trigger is get back an identity value. I have often written stored procedures like this:

    ...
    INSERT INTO dbo.Card (
        -- CardID
        [non-identity column list]
    )
    VALUES (
        [variable list]
    )
 
    -- Error check
 
    SELECT @CardID = SCOPE_IDENTITY()

This allows me to return the value of the identity column in an OUTPUT variable. This is useful to non-SQL calling code, but it is also very valuable when calling stored procedures from other stored procedures or in unit testing.

With views and “instead of insert” triggers, I don’t see how this can be done. If you are a trigger expert or just a creative programmer, please drop a comment in the forum about how to make this happen.

Conclusion

I see two valuable lessons from this research. First, simple same-database views and “instead of insert” triggers add negligible cost over accessing the base tables. This may not sound too important, and it may match what you expected, but it is important to have evidence that proves that it’s true.

Second, there is some cost (and we have actual numbers), but you can create a facade in a separate database that allows [most] code to remain unchanged by presenting views that mimic the original objects.

So, if you can create your facade in the same database as your base objects, do. If you can’t (for instance, if you have to create views that have the same name as base objects), you will pay a price, but you have evidence of what that price will be.

Download files for part 14 of this series.