I have this SQL query, but I've found that it can take up to 11 seconds to run. I'm really confused because when I change the date selection to a 2018 date, it returns instantly.
Here's the query:
select
cv.table3ID, dm.Column1 ,dm.Column2, mm.Column1,
convert(varchar, cv.Date, 107) as Date,
mm.table2ID, dm.table1ID, mm.Column2,
count(ctt.table4ID) as Total
from
table1 dm
inner join
table2 mm on mm.table2ID = dm.table1ID
inner join
table3 cv on cv.table3ID = mm.table2ID
left join
table4 ct on ct.table4CVID = cv.table3ID
inner join
table4 ctt on ctt.table4MMID = mm.table2ID
where
ctt.table4Date >= '2019-01-19'
and ct.table4CVID is null
and dm.Column1 like '%Albert%'
and cv.Column1 = 39505
and cv.Status = 'A'
group by
cv.table3ID, dm.Column1 ,dm.Column2, mm.Column1,
cv.Date, mm.table2ID, dm.table1ID, mm.Column2
I've found that when I execute that query with ctt.table4Date >= '2018-01-19', the response is immediate. But with '2019-01-19', it takes 11 seconds.
Initially, when I found that the query took 11 seconds, I thought it had to be an indexing issue, but I'm not sure any more if its got to do with the index since it executes well for an older date.
I've looked at the execution plan for the query with the different dates and they look completely different.
Any thoughts on why this might be happening? Does it have anything to do with updating the statistics?
[Update]
This image below is the comparison of the execution plan between 2018 and 2019 for table4 ctt. According to the execution plan, this takes up 43% of the operator cost in 2018 and 45% in 2019. Execution Plan comparison of table4 ctt 2019 and 2018. Top is 2019, bottom is 208
The image here is the comparison of the execution plan again for table4 as ct. Same here, top is 2019 and bottom is 2018. Execution plan of table4 ct comparison 2019 and 2018. Top is 2019, bottom is 208
[Update 2]
Here are the SQL Execution Plans:
When using '2018-01-19' as the date: https://www.brentozar.com/pastetheplan/?id=SyUh8xXQV
When using '2019-01-19' as the date: https://www.brentozar.com/pastetheplan/?id=rkELW1Q7V
The problem most likely is the fact that more rows are being returned from the other tables. The clustered index scan that you have linked with your [update] just shows the clustered index seek.
You do, however, need to realise that the number of times the index seek is being invoked is 144. and the actual number of rows read is in 8 digits which is causing the slow response.
I'm guessing that when this works fine for you, the actual number of executions on this table would be 1. The 144 is killing you here; given the poor seek predicates. If you know the query plan that works for you, and the indexes are already present to back it up you should forceseek plans and give explicit hints to join in particular order.
Edit
Took a look at the shared plans, changing the date to 2018 works faster for you since SQL switches to using a Hash Match in place of a loop join given the amount of data being processed.