1

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.

Any insight?

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.

Jose
  • 167
  • 2
  • 7
  • I think this would be better suited for SO... – Jakub Dec 22 '09 at 16:53
  • I thought this was for sql query questions... I'm confused where to post the questions – Jose Dec 22 '09 at 17:07
  • there are a few overlap areas between SO and SF, this is definitely a gray area. Feel free to ask SQL questions and see what input you get. If nobody bites, we can always move it over to SO to see what they have to say. – Kara Marfia Dec 22 '09 at 19:45
  • SQL is a programming language, so I'd say it needs to go to SO. – gbjbaanb Dec 23 '09 at 00:17

1 Answers1

1

On the index on AdjustDetailID, include the columns Fee and FeeTax. Otherwise the system will need to do a lookup to get that data, and the system will probably end up doing a scan, ignoring your index.

Rob Farley
  • 688
  • 2
  • 5
  • 13