patternsqlModerate
What I can do to speed up Key Lookup in my Query
Viewed 0 times
canwhatquerylookupspeedkey
Problem
I am using SQL2005 and against them I calling query simila to this
This is query is slow when I take look at Execution plan I see that is Cost of 71 % is taken by
Key Lookup.
What I can do to seed up this query.
declare @art table (id int primary key, naziv varchar(35) null, sifra varchar(15) null, jm int null);
insert into @art
select id, left(naziv,35), left(sifra,15), jm from art a
where (a.id = @art_id@ or @art_id@ = 0 )
Select
s.art_id
,sum(kol) as kol
,sum(kol*mpc) as MPCI
/*--and meny others --/
from doc d
inner join dokumenti dk on (d.tip = dk.tip)
inner join sdo s on (d.id=s.doc_id)
where
d.datum between @do_datuma@ and @do_datuma@
and dk.prodaja = 1
and (dk.Mal_Vel in (@Mal_Vel@))
and (d.skl_id = @skl_id@ or (@skl_id@ = 0))
and ((d.skl_id in (select skl_id from skladiste_gruperj where grp_id = @gr_rj_id@)) or ( @gr_rj_id@ = 0))
and (d.par_id = @par_id@ or @par_id@ = 0)
and s.art_id in (select id from @art)
group by s.art_idThis is query is slow when I take look at Execution plan I see that is Cost of 71 % is taken by
Key Lookup.
What I can do to seed up this query.
Solution
Remove it by making the index covering
That is, add an INCLUDE clause to the index so all columns needed are in the index.
That is, add an INCLUDE clause to the index so all columns needed are in the index.
Context
StackExchange Database Administrators Q#5085, answer score: 12
Revisions (0)
No revisions yet.