x

Execution plan or client statistics?

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

Select col1, 
col2, 
col3,
case   
when colX in (select ID from table2 where colA between thisdate and thatdate) then 'XYZ'
when colX in (select ID from table3 where colB between thisdate and thatdate) then 'ABC'
else    'Normal'
end,    
col4, 
col5, 
col6 
from table1
where col7=0 and col8 ='string' and col9 between thisdate and thatdate

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]

more ▼

asked Nov 12 '09 at 12:56 PM in Default

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

if you've got client stats, I presume you have an actual plan too?
Nov 12 '09 at 02:12 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Nov 12 '09 at 06:34 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1933
x977

asked: Nov 12 '09 at 12:56 PM

Seen: 2146 times

Last Updated: Nov 20 '09 at 09:06 AM