TSQL Slow Query, not using index as expected

Question

I have a wide table, relatively large, 14,264,775 rows, running on Azure SQL database.

The following query is in need of some TLC.

IF EXISTS (
    SELECT  1/0 
    FROM dbo.table1 src
      INNER JOIN dbo.table1 tgt 
        ON tgt.Col1 = src.Col1
    WHERE tgt.ValidFrom <= src.ValidTo
    AND tgt.ValidTo >= src.ValidFrom
    AND tgt.RecordId <> src.RecordId  
)
 BEGIN
    RAISERROR('Overlap detected in dbo.table1', 11, 1); 
 END ;

I have this index.

CREATE NONCLUSTERED INDEX [IX__table1] ON dbo.table1
        ( Col1 ) 
INCLUDE (ValidFrom, ValidTo, RecordId)
GO

This is the io stats from the query. The logical reads are through the roof.

Here's the plan XML. I tried PasteThePlan but it wouldn't parse the plan XML. (perhaps it doesn't like Axure sql database plan xml).

As you can see there's an index scan on [src] ; reading 14,264,775 rows (the same number as all the rows in the table) . And an index seek on [tgt] ; reading 194,405,307 rows.

What do I need to change to improve the performance of the query?

There are 2.1 million unique Col1 values amongst the 14 million total rows.

Paul White · Accepted Answer · 2022-09-13T13:16:41.183

You appear to have manually edited the XML and made it invalid, primarily by adding invalid characters like < and >. After fixing some issues, I was able to load the plan into SSMS and Plan Explorer:

This shows you have an index named [IX__dbo_table1__DateRange]—not mentioned in the question. Judging by the seek predicate, this index has at least Col1 and ValidTo in the index keys.

Another problem is the use of IF EXISTS. This introduces a row goal, which causes the optimizer to favour a nested loops solution. See the related Q & A IF EXISTS taking longer than embedded select statement.

That said, finding any possible overlapping range is a difficult problem to solve completely with a b-tree index, see Resolving a performance issue with BETWEEN join - eager spool.

Without knowing the complete table definition, indexes, and data distribution it's difficult to suggest a proper solution. If you just want something quick and easy to try without changing the indexes or source query too much, try a hash join hint:

IF EXISTS (
    SELECT  1/0 
    FROM dbo.table1 src
      INNER HASH JOIN dbo.table1 tgt -- hint added
        ON tgt.Col1 = src.Col1
    WHERE tgt.ValidFrom <= src.ValidTo
    AND tgt.ValidTo >= src.ValidFrom
    AND tgt.RecordId <> src.RecordId  
)
 BEGIN
    RAISERROR('Overlap detected in dbo.table1', 11, 1); 
 END ;

This will fully scan the index twice, but this may not be too bad if your system can handle the memory or I/O requirements, and if parallelism or batch mode execution is available. This will work best if there are a decent number of different Col1 values.

Assuming overlaps should be disallowed, my preference would be to avoid the situation occurring in the first place using constraints. See What is the correct way to ensure unique entries in a temporal database design?

Or, as ypercubeᵀᴹ suggested in chat:

IF EXISTS
(
    SELECT 1/0
    FROM
    (
        SELECT 
            T.ValidFrom,
            PrevValidTo =
                LAG(T.ValidTo) OVER ( 
                    PARTITION BY T.Col1
                    ORDER BY T.ValidFrom)
        FROM dbo.table1 AS T
    ) AS T1
    WHERE 
        T1.PrevValidTo >= T1.ValidFrom
)
BEGIN
    RAISERROR('Overlap detected in dbo.table1', 11, 1); 
END;

With an index like:

CREATE NONCLUSTERED INDEX [IX__table1] 
ON dbo.table1
    (Col1, ValidFrom) 
INCLUDE 
    (ValidTo, RecordId);

score 0 · Answer 2 · answered Sep 13 '22 at 12:03

Currently all records will be read because you are querying the records where tgt.Col1 = src.Col1, which is the complete table.

You should make the index more selective by adding ValidFrom and/or ValidTo to the index.

When adding those to the index, that columns can be removed after the INCLUDE parts.

TSQL Slow Query, not using index as expected

2 Answers2