How to avoid a Costly BookMark Lookup
Posted on 2006-11-27
Hi I have the following SQL. The Quey returns around 20,000 rows. The Exhibitior_List table is being accessed via a Non clsutered index.
However all the rows are being looked up in the clustered index. The index depth is 2 for the clutered index. So I gather for each value found in the non clustred index there are2 page reads for the lookup. This appears to be costly as the lookup is costing 76% of the query. The query takes about 1min 20 secs to run. Is there any way I can aviod this lookup. I'm not sure if I can create a wide covering index for all the rows being accessed in the query. Is this sensible considering there are about 8 or 9 columns. Is there any other strategy i can use to reduce the cost of the lookup. here is the query:
from Exhibitor_List el WITH (NOLOCK) left outer join tblExhibitorParticipant tp (NOLOCK)
ON el.K_el_id = tp.iExhibitoriD
left outer join tblParticipant p
ON tp.iParticipantID = p.iParticipantID
left outer join tblCountries tc
ON tc.iCountryID = el.iCountryID
inner join tblContinent con (NOLOCK)
ON tc.iContinentID = con.iContinentID
and el.var_el_active <> 'N'
and el.K_EV_id =100458 and
p. iEventid = 100458
ORDER BY dRegistrationDate