richard.austin (7/5/2013)
Thank you, but i am running your revised query and it too is taking a long time. It is worth mentioning that I have since added a join hint to the query (tried all three hash, merge and loop) and the query returned in seconds. There is something that is causing the sql optimiser to take a long time to compile an inefficient plan.This is just one example of a query ran by a third party application, so I cannot change the actual code that they run.
Can you post the actual plan for the quick version using the join hint? That's interesting...
You should refer them to the WHERE clause of the query. Here it is again:
WHERE (c."Start Date"<{ts '2013-06-30 12:55:40'}
AND c."Leaving Date" IS NULL
AND c."Payroll Name"=N'CCF'
OR c."Start Date"<{ts '2013-06-30 12:55:40'}
AND c."Leaving Date">={ts '2013-06-01 12:55:36'}
AND c."Payroll Name"=N'CCF')
Bet you can't guess what it's supposed to mean.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden