3

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.

TT.
  • 15,774
  • 6
  • 47
  • 88
Michal Řehout
  • 123
  • 11
  • Try updating statistics of that table. This may be one reason that Query plan not considering your index.. – Shakeer Mirza Nov 12 '16 at 12:15
  • 1
    Wouldn't help, as pointed out in the article posted by TT, the thing is that we use standard edition, which does the expand thing all the time, unless forced not too. – Michal Řehout Nov 15 '16 at 12:22

1 Answers1

4

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.

Community
  • 1
  • 1
TT.
  • 15,774
  • 6
  • 47
  • 88
  • That works like a charm, but why the sql server does not use it implicitly? The thing is, I would like to map the view to EF code-first entity. Which, to be done easily, must be a simple query with no hints. – Michal Řehout Nov 12 '16 at 12:17
  • @MichalŘehout The query optimizer does its best, but isn't always right. I assume that from heuristics or some insights from the engine developers (which I am not) on query performance, the default behavior is to try expand the view and use underlying table(s). Sometimes you have to hold the engine's hand and tell it to do it another way. That's why such explicit query and table hints exist. – TT. Nov 12 '16 at 12:21
  • Thank you. I will elaborate more on this, but this is the right answer. – Michal Řehout Nov 12 '16 at 12:22
  • @MichalŘehout You may gain more insights from this article on sqlperformance.com: [Indexed Views and Statistics](https://sqlperformance.com/2014/01/sql-plan/indexed-views-and-statistics) – TT. Nov 12 '16 at 12:38
  • Oh now I see - the problem is clearly that we are on standard edition. – Michal Řehout Nov 15 '16 at 12:21
  • @MichalŘehout I read that as well, but somewhere it says: *"On an Enterprise Edition instance, the query optimizer **may** be able to use an indexed view even if the query does not mention the view explicitly"*. So there's no guarantee that in EE it will be different. – TT. Nov 15 '16 at 12:38
  • @MichalŘehout Btw, did you get that table hint in your query in EF? I have no exp with EF so I'm interested to know. – TT. Nov 15 '16 at 12:41
  • yeah, but it is pretty sure, that it will not use it on standard – Michal Řehout Dec 05 '16 at 10:28
  • 1
    I don't think it is possible to do it somehow easily in EF6, right now I am using raw sql query, which is better anyway when you need to optimize for performance. – Michal Řehout Dec 05 '16 at 10:30