I am struggling to setup an indexed view in the following scenario so that the following query performs without two clustered index scans. Whenever I create an index view for this query and then use it, it seems to ignore any index I put on it:
-- +++ THE QUERY THAT I WANT TO IMPROVE PERFORMANCE-WISE +++
SELECT TOP 1 *
FROM dbo.TB_test1 t1
INNER JOIN dbo.TB_test2 t2 ON t1.PK_ID1 = t2.FK_ID1
ORDER BY t1.somethingelse1
,t2.somethingelse2;
GO
The table setup is as follows:
- two tables
- they are joined by an inner join by the query above
- and ordered by a column from the first and then a column from the second table by the query above; only TOP 1 is selected
-
(in the script below there are also some lines to generate test data, just in case it helps reproducing the problem)
-- +++ TABLE SETUP +++ CREATE TABLE [dbo].[TB_test1] ( [PK_ID1] [INT] IDENTITY(1, 1) NOT NULL ,[something1] VARCHAR(40) NOT NULL ,[somethingelse1] BIGINT NOT NULL CONSTRAINT [PK_TB_test1] PRIMARY KEY CLUSTERED ( [PK_ID1] ASC ) ); GO create TABLE [dbo].[TB_test2] ( [PK_ID2] [INT] IDENTITY(1, 1) NOT NULL ,[FK_ID1] [INT] NOT NULL ,[something2] VARCHAR(40) NOT NULL ,[somethingelse2] BIGINT NOT NULL CONSTRAINT [PK_TB_test2] PRIMARY KEY CLUSTERED ( [PK_ID2] ASC ) ); GO ALTER TABLE [dbo].[TB_test2] WITH CHECK ADD CONSTRAINT [FK_TB_Test1] FOREIGN KEY([FK_ID1]) REFERENCES [dbo].[TB_test1] ([PK_ID1]) GO ALTER TABLE [dbo].[TB_test2] CHECK CONSTRAINT [FK_TB_Test1] GO -- +++ TABLE DATA GENERATION +++ -- this might not be the quickest way, but it's only to set up test data INSERT INTO dbo.TB_test1 ( something1, somethingelse1 ) VALUES ( CONVERT(VARCHAR(40), NEWID()) -- something1 - varchar(40) ,ISNULL(ABS(CHECKSUM(NewId())) % 92233720368547758078, 1) -- somethingelse1 - bigint ) GO 100000 RAISERROR( 'Finished setting up dbo.TB_test1', 0, 1) WITH NOWAIT GO INSERT INTO dbo.TB_test2 ( FK_ID1, something2, somethingelse2 ) VALUES ( ISNULL(ABS(CHECKSUM(NewId())) % ((SELECT MAX(PK_ID1) FROM dbo.TB_test1) - 1), 0) + 1 -- FK_ID1 - int ,CONVERT(VARCHAR(40), NEWID()) -- something2 - varchar(40) ,ISNULL(ABS(CHECKSUM(NewId())) % 92233720368547758078, 1) -- somethingelse2 - bigint ) GO 100000 RAISERROR( 'Finished setting up dbo.TB_test2', 0, 1) WITH NOWAIT GO
The indexed view should probably be defined as follows and the resulting TOP 1 query is below. But what indexes do I need so that this query performs better than without the indexed view?
CREATE VIEW VI_test
WITH SCHEMABINDING
AS
SELECT t1.PK_ID1
,t1.something1
,t1.somethingelse1
,t2.PK_ID2
,t2.FK_ID1
,t2.something2
,t2.somethingelse2
FROM dbo.TB_test1 t1
INNER JOIN dbo.TB_test2 t2 ON t1.PK_ID1 = t2.FK_ID1
GO
SELECT TOP 1 * FROM dbo.VI_test ORDER BY somethingelse1,somethingelse2
GO
Best Answer
Unless you're using SQL Server Enterprise Edition (or equivalently, Trial and Developer), you will need to use
WITH (NOEXPAND)
on the view reference in order to use it. In fact, even if you are using Enterprise, there are good reasons to use that hint.Without the hint, the query optimizer (in Enterprise Edition) may make a cost-based choice between using the materialized view or accessing the base tables. Where the view is as large as the base tables, this calculation may favour the base tables.
Another point of interest is that without a
NOEXPAND
hint, view references are always expanded to the base query before optimization begins. As optimization progresses, the optimizer may or may not be able to match the expanded definition back to the materialized view, depending on previous optimization activity. This is almost certainly not the case with your simple query, but I mention it for completeness.So, using the
NOEXPAND
table hint is your main option, but you might also think about just materializing the base table keys and the columns needed for ordering in the view. Create a unique clustered index on the combined key columns, then a separate nonclustered index on the ordering columns.This will reduce the size of the materialized view, and limit the number of automatic updates that must be made to keep the view synchronized with the base tables. Your query can then be written to fetch the top 1 keys in the required order from the view (ideally with
NOEXPAND
), then join back to the base tables to fetch any remaining columns using the keys from the view.Another variation is to cluster the view on the ordering columns and table keys, then write the query to manually fetch the non-view columns from the base table using the keys. The best option for you depends on the broader context. A good way to decide is to test it with the real data and workload.
Basic solution
Execution plan:
Using a nonclustered index
Execution plan:
There is a lookup in this plan, but it is only used to fetch a single row.
Minimal Indexed View
Query:
Execution plan:
This shows the table keys being retrieved (a single row fetch from the view clustered index in order) followed by two single-row lookups on the base tables to fetch the remaining columns.