I have a parent table that contains a foreign key to a lookup table (simplified example):
CREATE TABLE [dbo].[Parent] ( [Id] [uniqueidentifier] NOT NULL, [LookupId] [uniqueidentifier] NULL ) CREATE TABLE [dbo].[Lookup] ( [Id] [uniqueidentifier] NOT NULL, [Name] [nvarchar](64) NOT NULL )
In this case, the
Parent table has over 10 million rows and the
Lookup table has around 5,000. The real
Parent implementation has several such foreign key references to other tables and each of those columns may contain NULLs.
Both example tables have unique clustered indexes for their
Parent has a non-clustered index for
Lookup has a non-clustered index for
I'm running a paged query where I want to include the lookup value in the results:-
SELECT P.Id, L.Name FROM Parent P LEFT JOIN Lookup L ON P.LookupId = L.Id ORDER BY P.Id OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY
This runs quickly, as does ordering by
If, however, I try to order by
Name (or even
L.Id), the query runs considerably slower:
SELECT P.Id, L.Name FROM Parent P LEFT JOIN Lookup L ON P.LookupId = L.Id ORDER BY L.Name OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY
The query plan for the second query is here: https://www.brentozar.com/pastetheplan/?id=Sk3SIOvMD
Other seemingly related questions seem to involve ordering by columns in the first table which could be resolved using an appropriate index.
I tried creating an indexed view for this query, however, SQL Server won't allow me to index the view because it contains a LEFT JOIN which I require because
LookupId may be NULL and if I use an INNER JOIN those records would be excluded.
Is there a way to optimise this situation?
Rob Farley's answer (thanks!) is great and works perfectly for the question as I originally asked it, in which I implied I was joining a single table.
As it is, I have multiple such tables and I was unable to reconcile all using INNER JOINs in order to use that solution.
For the moment I have worked around this by adding a "NULL" row to the lookup tables so I can use an INNER JOIN without losing any rows on the left.
In my case I use
uniqueidentifier identities, so I create an indexed view like this:
CREATE VIEW [dbo].[ParentView] WITH SCHEMABINDING AS SELECT P.Id, L.Name FROM [dbo].Parent P INNER JOIN [dbo].Lookup L ON ISNULL(P.LookupId, '00000000-0000-0000-0000-000000000000') = L.Id
I then add a row to the
Lookup table with a value of
Id so there is always a match on the right of the join.
I can then create indexes on that view as needed.
Also, as I'm not using Enterprise, I found I needed to use the
NOEXPAND hint to ensure those indexes are used:
SELECT * FROM [ParentView] WITH (NOEXPAND) ORDER BY Name OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY