Why is the following query slow?
select count(*)
from [dbo].[mt_dispatch_link]
, [dbo].[_mt_dispatch] [_mt_dispatch]
where (mt_dispatch_link.contract_id_1 = _mt_dispatch.contract_id
and mt_dispatch_link.dispatch_id_1 = _mt_dispatch.dispatch_id)
or (mt_dispatch_link.contract_id_2 = _mt_dispatch.contract_id
and mt_dispatch_link.dispatch_id_2 = _mt_dispatch.dispatch_id)
It takes over 10 minutes, and then I tend to stop it at that point. My question is more about how to make sense of the query plan.
Looking at the query plan I can see that the bottom clustered index scan is returning about 250000 records, but the cost is 0% and it is being put into a temporary table.
The top index scan is about 25000 records.
But the cost of 95% comes from the nested join. What conclusions should I draw from this?
The above query plan shows two index scans, does that mean it is doing 25000 + 250000 index scans, or does it mean that it is doing 25000 * 250000 index scans?
If I change the query to this (adding FORCESEEK
):
select count(*)
from [dbo].[mt_dispatch_link]
, [dbo].[_mt_dispatch] [_mt_dispatch]
WITH (FORCESEEK)
where (mt_dispatch_link.contract_id_1 = _mt_dispatch.contract_id
and mt_dispatch_link.dispatch_id_1 = _mt_dispatch.dispatch_id)
or (mt_dispatch_link.contract_id_2 = _mt_dispatch.contract_id
and mt_dispatch_link.dispatch_id_2 = _mt_dispatch.dispatch_id)
I end up with a much better plan and the query runs instantly:
I ran update statistics on both tables. Didn't fix it unfortunately. The table design is not very good, so I think SQL Server doesn't really understand and is thus coming up with a poor query plan. Some more info about the table design at How to Optimise Query.
Why is the query optimiser not coming up with the optimum plan?