HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

What I can do to speed up Key Lookup in my Query

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
canwhatquerylookupspeedkey

Problem

I am using SQL2005 and against them I calling query simila to this

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_id


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.

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.

Context

StackExchange Database Administrators Q#5085, answer score: 12

Revisions (0)

No revisions yet.