Sql-server – SQL Server Index Scan when using ‘OR’ operator

nonclustered-indexoptimizationsql server

We implemented a Google-style search where SQL queries are run after a debounce is triggered from the front-end. (We know SQL is probably the wrong technology for this, but I'm knee-deep in startup-chaos here.) The query:

SELECT 
    TOP(50) [Name], [Surname]
FROM 
    [dbo].[Clients]
WHERE 
    [Name] LIKE @SearchTerm + '%' OR
    [Surname] LIKE @SearchTerm + '%'

It is a sizable table, so I added two non-clustered indexes on both columns to help speed things up:

CREATE NONCLUSTERED INDEX [IX_Patients_Name] ON [dbo].[Clients]
(
    [Name] ASC
)
INCLUDE([Surname]);

CREATE NONCLUSTERED INDEX [IX_Patients_Surname] ON [dbo].[Clients]
(
    [Surname] ASC
)
INCLUDE([Name]);

My thinking was that SQL would do an index seek on both columns, but it seems the query optimizer decides to use an index scan.

Clustered Index Seek

This might not be a real issue for this simple use-case, but we have more complex versions of this with multiple joins, etc.

Is there any way to optimize this query to uses seeks?

Best Answer

As you mentioned in your question, this kind of Google-style query isn't really what SQL Server is "good at." Erik Darling talked about this exact query anti-pattern in his post The Only Thing Worse Than Optional Parameters….

All that aside.

It's possible to naturally get a seek with that type of query, but it's much more common to get the scan as you noticed. Here's an example from the StackOverflow2010 sample database.

First I'll create these two helpful indexes:

CREATE NONCLUSTERED INDEX IX_DisplayName ON dbo.Users (DisplayName) INCLUDE ([Location]);
CREATE NONCLUSTERED INDEX IX_Location ON dbo.Users ([Location]) INCLUDE (DisplayName);
GO

Then I'll create a procedure similar to the one you have:

CREATE OR ALTER PROCEDURE dbo.sp_Test
    @SearchTerm nvarchar(100)
AS
BEGIN;
    SELECT TOP (50)
        DisplayName, 
        [Location]
    FROM 
        dbo.Users
    WHERE 
        DisplayName LIKE @SearchTerm + '%' OR
        [Location] LIKE @SearchTerm + '%'
END;
GO

If I run that procedure with a fairly selective parameter, I'll end up with an index union plan. If the parameter is less selective, a scan of one of the covering indexes is used instead.

DBCC FREEPROCCACHE;
GO
EXEC dbo.sp_Test @SearchTerm = N'Josh';
GO
DBCC FREEPROCCACHE;
GO
EXEC dbo.sp_Test @SearchTerm = N'S';
GO

Screenshot of the index union and scan plans from pastetheplan.com

Execution plans are here.

Note that this is true even if you write this as separate UNION queries directly.

One way to reliably get the index union plan, as mentioned in the linked post, is to add a FORCESEEK hint to the table you'd like to union on.

If I change the proc to this, I don't get scans on either plan:

CREATE OR ALTER PROCEDURE dbo.sp_Test
    @SearchTerm nvarchar(100)
AS
BEGIN;
    SELECT TOP (50)
        DisplayName, 
        [Location]
    FROM 
        dbo.Users WITH (FORCESEEK)
    WHERE 
        DisplayName LIKE @SearchTerm + '%' OR
        [Location] LIKE @SearchTerm + '%'
END;
GO

The bigger issue with the query, as simplified in the question anyway, is that you are using TOP without an ORDER BY, which is likely to produce drastically different search results depending on which index is used. Make sure your real query has an ORDER BY, or that this problem is accounted for in some way.

Related Question