I need to do a query by datepart(day, BornDate)
and/or month, on a table that has several million rows, which is vey CPU intenstive.
I tried using indexed view with the datepart columns, even creating nonclustered index on the datepart(day, BornDate)
column in the indexed view itself. But the execution plan still tells me that the query is being computed using datepart
on the underlying table.
The query I run, is the following:
set statistics time on
SELECT count(1) FROM [dbo].[DemandsBornDateParts] where borndateday = 5 OPTION (RECOMPILE)
set statistics time off
I compare it alway to the same query directed to the underlying table:
set statistics time on
select count(1) from dbo.Demands where DAY(borndate) = 5
set statistics time off
They both show almost identical query plans, with nearly the same subtree cost, CPU and elapsed time, both doing a clustered index scan with predicate datepart(day,[dbo].[Demands].[BornDate])=(5)
The view is defined like this:
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('dbo.DemandsBornDateParts', 'view') IS NOT NULL
DROP VIEW dbo.DemandsBornDateParts ;
GO
CREATE VIEW dbo.DemandsBornDateParts
WITH SCHEMABINDING
AS
SELECT id,
Datepart(DAY, borndate) AS BornDateDay,
Datepart(MONTH, borndate) AS BornDateMonth,
Datepart(YEAR, borndate) AS BornDateYear
FROM DBO.demands
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX [PK_dbo.DemandsBornDateParts]
ON dbo.DemandsBornDateParts (Id);
GO
CREATE NONCLUSTERED INDEX [IX_BornDateDay] ON [dbo].[DemandsBornDateParts]
(
[BornDateDay] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
How can I achieve to use the persisted/indexed columns, without recalculating the datepart
over and over again? I can not use persisted columns, I need to use a view.
Add WITH(NOEXPAND)
after the view's name in a query. From the documentation on Table Hints:
NOEXPAND
Specifies that any indexed views are not expanded to access underlying tables when the query optimizer processes the query. The query optimizer treats the view like a table with clustered index. NOEXPAND applies only to indexed views.
And from Remarks on Using NOEXPAND on the same page:
To force the optimizer to use an index for an indexed view, specify the NOEXPAND option.