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, 
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'
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?



[Edit: Yes Kev, sorry, I meant Execution plan not estimated ...

All references changed, I think


more ▼

asked Nov 12, 2009 at 12:56 PM in Default

avatar image

Fatherjack ♦♦
43.8k 79 101 118

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

Nov 12, 2009 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, 2009 at 06:34 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Nov 12, 2009 at 12:56 PM

Seen: 2595 times

Last Updated: Nov 20, 2009 at 09:06 AM

Copyright 2018 Redgate Software. Privacy Policy