I have been working on a report and took a look at the query that was retrieving the data. As always, I thought I could do better ...! I have the original query in a query editor and have the re-written version beneath it. the queries are separated by a GO command.
When I view the execution plan it shows the original query as 66% of the total cost and my new version as 34% but the client statistics shows that in all but the "wait time on server replies" the original query is better...
the essence of the original query is
ColX is FK in table1 and ID is PF in tables2 + 3
I took out the case as it stands and select the rows into a temp table and join to it.
The result sets are identical.
Can anyone explain why the plan and the statistics are seamlingly contradictory (or am I reading it wrongly) and which should I trust?
[Edit: Yes Kev, sorry, I meant Execution plan not estimated ...
All references changed, I think
I think Kev should post an answer, because his comment is worth 10 rep.
Client stats are less reliable than server based ones, which in turn are less reliable than the actual plan. Base your judgements on an actual plan, if you can.
answered Nov 12 '09 at 06:34 PM
Matt Whitfield ♦♦