Causing SQL to prefer casted time seek in view query
0
votes
0
answers
49
views
We have ETL process executed by Kafka. It can only get and query by
datetime2
date data type.
It generates queries like this:
EXEC sp_executesql N'SELECT *
FROM ViewName
WHERE ViewName.UpdateDate > @P0
AND ViewName.UpdateDate '2022-07-01'
--Can add UpdateDate > DATEADD(d, -2, GETDATE())
GO
But in this case SQL decides first to scan this index and only then apply parameters filtering causing heavy reads. I can add commented out filter to reduce reads, but I'm searching for solution to make SQL "understand" that it can use parameters instead. We cannot create non filtered index because of table size. Also adding Cast to UpdateDate
in where clause causes SQL not to use index at all.
Is there something that can be done in this case code wise?(Forceseek
hint throws error)
Asked by Michael Cherevko
(742 rep)
Aug 6, 2024, 02:13 PM