declare @DateFrom datetime declare @DateTo datetime set @DateFrom = '20161130' set @DateTo = '20161201' declare @dindexFrom datetime Declare @dindexTo datetime set @dindexFrom = @DateFrom - 30 set @dindexTo = @DateTo + 30 SELECT * FROM tblTrans_Cash with (index = indDateTime) CROSS JOIN tblCinema C where TransC_strType in ('M') and TransC_dtmRealTransTime BETWEEN @DateFrom AND @DateTo and TransC_dtmDateTime BETWEEN @dindexFrom AND @dindexTo ORDER BY 1
I have a table with a missing index (TransC_dtmRealTransTime) , as I cannot insert it because I am not allowed as it is a 3rd party db, I am forcing an existing index on the query so I can at least get a non clustered RIP lookup and an indexd seek on tbltrans_cash ( you can see the pictures).
In that way I get a 2 seconds vs 18 seconds(without the forced index).
Is there anything else I can try, knowing that I cannot created indexes on the table?
And also there is no clustered index on the table.
Can't seem to find a better solution by myself.
And this is a simple query that is sargable, as you can see.
P.S. and TransC_dtmDateTime BETWEEN @dindexFrom AND @dindexTo is actually not needed but I trigger the index in that way, else the index will not work , even forced.