I looked for an attempt to optimize (or at least change) some EF code in C # for using stored procedures and found what seems anomalous (or something new for me) when searching for strings matching a constant list, Typical manual request manually will look like ...
SELECT Something FROM Table WHERE ID IN (one, two, others);
We had an EF request, which we replaced with a stored procedure call, so I looked at the result, saw that it was complex and thought that my simpler request (similar to the one above) would be better. This is not true. Here is a brief demo that reproduces this.
Can anyone explain why the execution plans for the final version are using
...WHERE EXISTS(... (SELECT 1 AS X) AS Alias UNION ALL...) AS Alias...)
Designbetter, presumably because it omits the expensive SORT operation, although the plan includes a TWO index scan, not one of the simpler queries.
Here is a separate example script (hopefully) ...
USE SandBox;
IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Test')
DROP TABLE Test;
CREATE TABLE Test (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, FormId INT NOT NULL, DateRead DATE NULL);
INSERT INTO Test VALUES (1, NULL), (1, GETDATE()), (1, NULL), (4, NULL), (5, NULL), (6, GETDATE());
SELECT T.FormId, COUNT(*) AS TheCount
FROM Test AS T
WHERE T.FormId IN (1, 5, 6)
AND T.DateRead IS NULL
GROUP BY T.FormId;
SELECT T.FormId, COUNT(*) AS TheCount
FROM Test T
WHERE EXISTS ( SELECT NULL
FROM (VALUES (1), (5), (6)
) AS X(FormId)
WHERE X.FormId = T.FormId
)
AND T.DateRead IS NULL
GROUP BY T.FormId;
SELECT T.FormId, COUNT(*) AS TheCount
FROM Test T
WHERE EXISTS ( SELECT NULL
FROM ( SELECT 1
UNION ALL
SELECT 5
UNION ALL
SELECT 6
) AS X(FormId)
WHERE X.FormId = T.FormId
)
AND T.DateRead IS NULL
GROUP BY T.FormId;
SELECT T.FormId, COUNT(*) AS TheCount
FROM Test T
WHERE EXISTS ( SELECT NULL
FROM ( SELECT 1 FROM (SELECT 1 AS X) AS X1
UNION ALL
SELECT 5 FROM (SELECT 1 AS X) AS X2
UNION ALL
SELECT 6 FROM (SELECT 1 AS X) AS X3
) AS X(FormId)
WHERE X.FormId = T.FormId
)
AND T.DateRead IS NULL
GROUP BY T.FormId;
Can someone help me to understand why and if there is any benefit for wider use for such a request format?
I looked around for something special in the materials (SELECT 1 AS X)
, and although many show that it is common in the output of EF, I have not seen anything about this particular obvious benefit.
Thanks in advance,
Whale