Indexing for complex predicates

Question

I'm struggling to identify effective indexes (or rewrite the query) to improve a query with the following confounding predicates:

JOIN on a date from one table being in range - between two date fields on second table (one is nullable, one is not nullable in PK).
The date used is actually the value in date field (nullable) +1.
WHERE clauses includes OR logic on multiple flag fields.

The simplified version of the query is:

select
d.dim_date_id
,f.dim_provider_id
,f.dim_event_id
,d.date

from DWH.dbo.tbl_fact_outcome f
join   DWH.dbo.tbl_dim_date d on DATEADD(DAY,1,d.date) between f.known_from and f.known_to 

where
f.known_from > getdate()-12
and (d.flag_latest_day = 'Y' or  d.flag_end_of_month = 'Y'  or (d.flag_end_of_week = 'Y' AND d.flag_latest_week = 'Y'))
and d.flag_future_day = 'N'
and f.deleted = 0

tbl_fact_outcome has these indexes:

PK clustered index on input_form_id, known_from
Non-unique Nonclustered index on deleted, known_from, known_to (INCLUDES the required _dim_id fields)

tbl_dim_date has these indexes:

PK clustered index on dim_date_id
Non-unique nonclustered index on flag_future_day, date (INCLUDES relevant flag fields)

At present, it estimates 853 rows but returns 16,784.

Here is the query plan: https://www.brentozar.com/pastetheplan/?id=rydKb_3AI

Statistics are up to date. I have tried re-ordering the covering indexes but no improvement.

I'm totally stumped as to what else to try with indexes or the code itself to improve performance, so any pointers appreciated.

EDIT 05/07/2020

Ruled out following suggestions here:

Filtered index (on deleted) on tbl_fact_outcome - less than 1% of records would be filtered out, so not worthwhile
Filtered index (using entire WHERE clause from query) on tbl_dim_date - not possible to use OR in index
Index on tbl_dim_date with INCLUDEd fields as key fields - tried this, made no difference, not used by optimizer.

Do you have filter conditions on your indexes or are they non-filtered indexes? — Preben Huybrechts, Jul 03 '20 at 09:08
What is the meaning of d.flag_future_day? I have bad feeling about this. — SMor, Jul 03 '20 at 13:14
SMor - the table is a list of dates, the field flag_future_day is a Y/N flag to identify dates which are in the future. — Joe Shark, Jul 03 '20 at 13:29

Preben Huybrechts · Answer 1 · 2020-07-03T09:38:55.143

Guessing all/most queries filter on deleted I would suggest a filtered index.

CREATE NONCLUSTERED INDEX TodoNewIndexName ON DWH.dbo.tbl_fact_outcome (
    known_from ASC
    ,known_to ASC
    )
INCLUDE (dim_event_id,dim_provider_id)
WHERE deleted = 0;

If this query is really running frequently you could also consider using a filterd index for tbl_dim_date. This will probably only be used by this query, since the where is an exact match of your query:

CREATE NONCLUSTERED INDEX TodoNewIndexName ON DWH.dbo.tbl_dim_date (DATE ASC)
    WHERE (
            d.flag_latest_day = 'Y'
            OR d.flag_end_of_month = 'Y'
            OR (
                d.flag_end_of_week = 'Y'
                AND d.flag_latest_week = 'Y'
                )
            )
        AND d.flag_future_day = 'N'

If you don't want a filterd index on the flag fields. You should add the flag fields to the index instead of includes.

 CREATE NONCLUSTERED INDEX TodoNewIndexName ON DWH.dbo.tbl_dim_date (
    DATE ASC
    ,flag_latest_day ASC
    ,flag_end_of_month ASC
    ,flag_end_of_week ASC
    ,flag_latest_week ASC
    )

What this should do is get rid of the Index Spool (Eager Spool) more info about eager spool.

Eager index spools are often a sign that a useful permanent index is missing from the database schema. This is not always the case, as the streaming table-valued function examples show.

Thanks for your reply. Re the filtered index on tbl_fact_outcome - less than 1% of records are flagged as deleted so in this case I don't think that would help. The filtered index on tbl_dim_date is not possible as SQL Server doesn't allow OR conditions in filters (SQL Server 2014 doesn't at least). I tried your final index on tbl_dim_date, but the optimizer sticks with the existing index (with those fields as INCLUDE) and still has the eager spool. — Joe Shark, Jul 05 '20 at 10:56

score 0 · Answer 2 · answered Jul 06 '20 at 10:08

0

Has your date dimension a nextDay column or something? If not you can add this column and replace DATEADD(DAY,1,d.date) with this new column.

answered Jul 06 '20 at 10:08

Wouter

2,881
2
9
22

It doesn't currently but that is something to consider if I can;t resolve this with indexes or query tuning. – Joe Shark Jul 06 '20 at 10:31

Indexing for complex predicates

2 Answers2