Linq compiled query uses clustered index instead of filtered index - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

Linq compiled query uses clustered index instead of filtered index

sql-server add tag

5 years ago Steve (imported from SE)

Given the following table

CREATE TABLE Test(
	Id INT PRIMARY KEY IDENTITY(1, 1),
	Val INT
)

CREATE UNIQUE INDEX UX_Test_Val ON Test(Val) WHERE Val IS NOT NULL

DECLARE @i INT = 0
WHILE (@i < 30000)
BEGIN
INSERT INTO Test(Val) VALUES(@i)
SET @i = @i + 1
END

xxxxxxxxxx
 
CREATE TABLE Test(    Id INT PRIMARY KEY IDENTITY(1, 1),    Val INT)​CREATE UNIQUE INDEX UX_Test_Val ON Test(Val) WHERE Val IS NOT NULL​DECLARE @i INT = 0WHILE (@i < 30000)BEGININSERT INTO Test(Val) VALUES(@i)SET @i = @i + 1END

and I have a Linq compiled query from a statement similar to

Test.Where(x => x.Val == 123).FirstOrDefault();

xxxxxxxxxx
 
Test.Where(x => x.Val == 123).FirstOrDefault();

this gets compiled to something similar to

DECLARE @V INT
SELECT TOP 1 Id FROM Test WHERE Val = @V OR (Val IS NULL AND @V IS NULL)

xxxxxxxxxx
 
DECLARE @V INTSELECT TOP 1 Id FROM Test WHERE Val = @V OR (Val IS NULL AND @V IS NULL)

Seems legit. But I am very disappointed when seeing the clustered index scan in the query plan.

The plan looks way better when I remove the second clause.

SELECT TOP 1 Id FROM Test WHERE Val = @V

xxxxxxxxxx
 
SELECT TOP 1 Id FROM Test WHERE Val = @V

This time the plan is a Nonclustered Index Seek with 1 row read.

So my first guess is that the filtered index is the evil here and I created a second non-clustered not unique index on the same column

CREATE INDEX IX_Test_Val ON Test(Val)

xxxxxxxxxx
 
CREATE INDEX IX_Test_Val ON Test(Val)

This time both statements use the newly-created index and yields the same index seek plan.

Three questions:

Why does the filtered index have such poor performance in this case?
How can I make Linq omit the IS NULL part?
Is there a way to enforce a unique constraint that allows NULL with just one non-filtered index?

Top Answer

5 years ago Josh Darnell (imported from SE)

Why does the filtered index have such poor performance in this case?

The query couldn’t use the filtered index, because of the IS NULL part. By definition, the query needs to consider rows that have a Val of NULL, and the filtered index doesn’t have those rows.

How can I make Linq omit the IS NULL part?

This isn’t really a database question, but…

LINQ is accounting for your parameter being nullable. In the example you gave, it’s not nullable (it’s an integer literal). In your real code, it’s probably a nullable int (int?) or a string, or some other reference type. Change the parameter to be non-nullable (or if it’s a nullable value type, call .Value) to remove the IS NULL part of the query.

Is there a way to enforce a unique constraint that allows NULL with just one non-filtered index?

If I understand your question, then no - I don’t think so. A UNIQUE constraint or index will allow 1 NULL value, but not multiple. However, the filtered index might work for your purposes once the query issue has been fixed.

1 Answer