Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query that is taking about 10 seconds to run and I am trying to optimize it.

All the time is spent in the key lookup which is fine however in this case if I mouse over the key lookup in the actual execution plan it says

Expected Rows : 1
Estimated Executions : 3341
Actual Rows : 0
Executions : 0

I understand in this case the statistics are expecting the index seek to find rows which is fine but as no rows were returned in the Index Seek why would the key lookup take any time? or even be shown in the query plan at all?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

The percentages on each operator that are shown in an "actual" plan are still estimates. The additions to an "actual" plan over "estimated" are the actual row counts and operator executes.

In the example you've given the key lookup was estimated to be executed 3341 times, which is why it has a high estimated percentage cost.

share|improve this answer
    
interesting, I had no idea that was the case. So what your saying is it sounds like the time is being spent elsewhere and not where the plan says it it? –  Gavin Sep 7 '11 at 16:50
1  
Yes, one of the other operators is consuming the time. Get a (free) copy of Plan Explorer from SQLSentry, it'll make it easier to identify where. –  Mark Storey-Smith Sep 7 '11 at 16:54
    
Wont that just display the same estimates in a different format? or are the numbers more accurate? –  Gavin Sep 7 '11 at 16:55
2  
Same data but lots of different ways of viewing it. Try it, you'll soon be wondering how you managed without it. If nothing stands out, start a new question and post the execution plan xml. –  Mark Storey-Smith Sep 7 '11 at 16:57

If this is an Oracle database, did the table in question have a lot a data at one time? I can't tell exactly from your original question, but it appears that the 'zero actual rows' means the table is empty? If so, I would think that the high water mark needs adjusting. Oracle remembers the highest storage requirements of the table, and even if no rows are returned, it will still scan to the high water mark to verify your query. The only way to reset the high water mark is to either truncate the table, or drop/re-create the table. Of course, any data would need to be imported.

share|improve this answer
    
Welcome to dba.se. The majority of questions will be tagged with the platform(s) of interest and these are shown just under the question text. –  Mark Storey-Smith Sep 7 '11 at 19:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.