I've got a query looking for accounts against the user-provided input:
SELECT * FROM accounts a
WHERE a.dep_id in :depIds
AND (
(a.key_identifier IS NULL AND a.name LIKE CONCAT(:searchText, '%'))
OR (a.key_identifier IS NOT NULL AND ca.hashed_name = :hashedSearchText)
OR ca.skac LIKE CONCAT(:searchText, '%')
)
Often the users don't put any searchText i. e. for such input the query will effectively be
--...
AND a.name LIKE '%'
i.e. every non-nullable string matches.
I was thinking of introducing the fast-return statement like:
SELECT * FROM accounts a
WHERE a.dep_id in :depIds
AND (
:searchText IS NULL
OR (
(a.key_identifier IS NULL AND a.name LIKE CONCAT(:searchText, '%'))
OR (a.key_identifier IS NOT NULL AND ca.hashed_name = :hashedSearchText)
OR ca.skac LIKE CONCAT(:searchText, '%')
)
)
Real execution plans turned out to be the same for both (clustered index scan) and both queries return the same number of rows when searchText is null. But when I've added to the query this command
SET STATISTICS IO, TIME ON;
I suddenly found out that the second query was actually slower. Here's the statistics for the original one:
(664525 rows affected)
Table 'account'. Scan count 1, logical reads 15384, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(2 rows affected)
(1 row affected)
SQL Server Execution Times:
CPU time = 1750 ms, elapsed time = 12013 ms.
and this one is for the patched:
(664525 rows affected)
Table 'account'. Scan count 1, logical reads 15384, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(2 rows affected)
(1 row affected)
SQL Server Execution Times:
CPU time = 1719 ms, elapsed time = 14419 ms.
So my question is whether it's possible to somehow implement fast-return on SQL level for the query above?