SqlCredit – Part 14: The Cost of Translation

Download files for part 14 of this series.

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.

Test

Insert Direct

Insert View

Penalty

Read Direct

Read View

Penalty

1

24,200

30,123

24.48%

3,673

4,080

11.08%

2

23,483

27,653

17.76%

3,670

4,073

10.98%

3

24,156

27,466

13.70%

3,683

4,093

11.13%

4

23,780

27,936

17.48%

3,686

4,076

10.58%

Average

23,905

28,295

18.36%

3,678

4,081

10.94%

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.

»


See All Articles by Columnist
Rob Garrison

Rob Garrison
Rob Garrison
Rob Garrison is a database architect at Fiserv in Portland, Oregon specializing in OLTP design and development. He has over twenty years of IT experience, with over ten years in database development and architecture.

Latest Articles