Indexed View looking for null references without INNER JOIN or Subquery

Question

So I have a legacy database with table structure like this (simplified)

Create Table Transaction
{
    TransactionId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    ReplacesTransactionId INT
    ..
    ..
}

So I want to create an indexed view such that the following example would return only the second role (because it replaces the first one)

Insert Into Transaction (TransactionId, ReplacesTransactionId, ..) Values (1,0 ..)
Insert Into Transaction (TransactionId, ReplacesTransactionId, ..) Values (2,1 ..)

There are a number of ways of creating this query but I would like to create an indexed view which means I cannot use Subqueries, Left joins or Excepts. An example query (using LEFT JOIN) could be.

SELECT trans1.* FROM Transaction trans1 
LEFT JOIN Transaction trans2 on trans1.TransactionId = trans2.ReplacesTransactionId
Where trans2.TransacationId IS NULL

Clearly I'm stuck with the structure of the database and am looking to improve performance of the application using the data.

Any suggestions?

If you run into an issue with a sensible query falling foul of the indexed view rules, it's likely that any re-writing of the query will fall foul of the same rule or another equally bad one. I can't see a fix the query you've shown. — Damien_The_Unbeliever, Oct 01 '13 at 10:31

score 0 · Accepted Answer · answered Oct 01 '13 at 17:56

What you have here is essentially a hierarchical dataset in which you want to pre-traverse the hierarchy and store the result in an indexed view, but AFAIK, indexed views do not support that.

On the other hand, this may not be the only angle of attack to your larger goal of improving performance. First, the most obvious question: can we assume that TransactionId is clustered and ReplacesTransactionId is indexed? If not, those would be my first two changes. If the indexing is already good, then the next step would be to look at the query plan of your left join and see if anything leaps out.

In general terms (not having seen the query plan): one possible approach could be to try and convert your SELECT statement to a "covered query" (see https://www.simple-talk.com/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/). This would most likely entail some combination of:

Reducing the number of columns in the SELECT statement (replacing SELECT *)
Adding a few "included" columns to the index on ReplacesTransactionId (either in SSMS or using the INCLUDES clause of CREATE INDEX).

Good luck!

Thank you very much most helpful. I was beginning to realise that what I was trying to achieve might be impossible. Nice to have someone else confirm. — user2834135, Oct 03 '13 at 09:16
One follow-on thought I had with this-- even though you are stuck with the structure, would you be able to create an additional table? I thought maybe so, since creating an additional view was an option. If so, remember that an "indexed view" is really just a table whose content is managed automatically. As such, you can "roll your own" by creating a helper table (even named like a view if you want) then use triggers to automatically populate it. Somewhat similar example, see http://stackoverflow.com/a/19101016/2824445 — Robert N, Oct 03 '13 at 23:37

Indexed View looking for null references without INNER JOIN or Subquery

1 Answers1