I have a query that I want to execute that fastest possible.
Here it is:
select d.InvoiceDetailId,a.Fee,a.FeeTax from InvoiceDetail d LEFT JOIN InvoiceDetail a on a.AdjustDetailId = d.InvoiceDetailId
I put an ascending index on AdjustDetailId column
I then ran the query with 'Show Actual Execution Plan' and the result estimated subtree cost(off of the topmost select node) was 2.07
I then thought, maybe I can do something to improve this so I added a conditional to the left join like so:
select d.InvoiceDetailId,a.Fee,a.FeeTax from InvoiceDetail d LEFT JOIN InvoiceDetail a on a.AdjustDetailId is not null and a.AdjustDetailId = d.InvoiceDetailId
I re-ran and I got a subtree cost of .98. So I thought, great I made it twice as fast. Well I then clicked show client statistics and then clicked execute 4-5 times with both queries and believe it or not the first query averaged out to be faster. I don't get it. By the way the query returns 120K rows.
Maybe i get tainted results because of caching, but I don't know if that is the case or how to reset the caching.
EDIT: Okay I googled how to clear query cache so I added the following before the queries:
DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE
I then ran each query 5 times and the first query was still a little faster(13%). 1st Query: Client Processing time: 239.4 2nd Query: Client Processing time: 290
So I guess the question is, why do you think so? Could it be when the table quadruples in size that the second query will be faster? Or the left join is causing the query to hit the index twice so it will always be slower.
Please don't flame me, I'm just trying to get educated.