|
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? thanks Jonathan [Edit: Yes Kev, sorry, I meant Execution plan not estimated ... All references changed, I think Jonathan]
(comments are locked)
|
|
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. I must say that I have pretty much always only referred to the execution plans [estimated and/or actual ;) ] but for some reason today I selected the client stats too and its the contradiction that has concerned me. I could understand a difference of opinion on how much better one was over the other but to have a difference of which one is better seems odd.
Nov 13 '09 at 05:45 AM
Fatherjack ♦♦
There's definite cases where that can happen - the prime example being a query that takes a lot of computation to produce 1 row, rather than a simple query that takes no computation to produce 10,000,000 rows. The latter would probably look hideous on client stats, even though it's not bugging the server too much in terms of computation...
Nov 13 '09 at 05:54 AM
Matt Whitfield ♦♦
(comments are locked)
|


if you've got client stats, I presume you have an actual plan too?