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]
