SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


help me remove key lookups from the query below


help me remove key lookups from the query below

Author
Message
ekant_alone
ekant_alone
Mr or Mrs. 500
Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)

Group: General Forum Members
Points: 542 Visits: 473
Hi,
I have a stupid written query. I saw that 99% of the cost is going into key look ups... i tried covering index but cant get rid of it. Can someone help me please?

declare @p1 int
--set @p1=2692
exec sp_prepexec @p1 output,N'@P0 varchar(8000),@P1 varchar(8000),@P2 varchar(8000),
@P3 varchar(8000),@P4 varchar(8000),@P5 varchar(8000),@P6 varchar(8000),@P7 varchar(8000),
@P8 varchar(8000),@P9 varchar(8000),@P10 varchar(8000),@P11 varchar(8000),@P12 varchar(8000),
@P13 varchar(8000),@P14 varchar(8000),@P15 varchar(8000),@P16 varchar(8000),@P17 varchar(8000),
@P18 varchar(8000),@P19 varchar(8000),@P20 varchar(8000),@P21 varchar(8000),@P22 varchar(8000),
@P23 varchar(8000),@P24 varchar(8000),@P25 varchar(8000),@P26 varchar(8000),@P27 varchar(8000),
@P28 varchar(8000),@P29 varchar(8000),@P30 varchar(8000),@P31 varchar(8000),@P32 varchar(8000),
@P33 varchar(8000),@P34 varchar(8000),@P35 varchar(8000),@P36 varchar(8000),@P37 varchar(8000),
@P38 varchar(8000),@P39 varchar(8000),@P40 varchar(8000),@P41 varchar(8000),@P42 varchar(8000),
@P43 varchar(8000),@P44 varchar(8000),@P45 varchar(8000),@P46 varchar(8000),@P47 varchar(8000),
@P48 varchar(8000),@P49 varchar(8000),@P50 varchar(8000),@P51 varchar(8000),@P52 varchar(8000),
@P53 varchar(8000),@P54 varchar(8000),@P55 varchar(8000),@P56 varchar(8000),@P57 varchar(8000),
@P58 varchar(8000),@P59 varchar(8000),@P60 varchar(8000),@P61 varchar(8000),@P62 varchar(8000),
@P63 varchar(8000),@P64 varchar(8000),@P65 varchar(8000),@P66 varchar(8000),@P67 varchar(8000),
@P68 varchar(8000),@P69 varchar(8000),@P70 varchar(8000),@P71 varchar(8000),@P72 varchar(8000),
@P73 varchar(8000),@P74 varchar(8000),@P75 varchar(8000),@P76 varchar(8000),@P77 varchar(8000),
@P78 varchar(8000),@P79 varchar(8000),@P80 varchar(8000),@P81 varchar(8000),@P82 varchar(8000),
@P83 varchar(8000),@P84 varchar(8000),@P85 varchar(8000),@P86 varchar(8000),@P87 varchar(8000),
@P88 varchar(8000),@P89 varchar(8000),@P90 varchar(8000),@P91 varchar(8000),@P92 varchar(8000),
@P93 varchar(8000),@P94 varchar(8000),@P95 varchar(8000),@P96 varchar(8000),@P97 varchar(8000),
@P98 varchar(8000),@P99 varchar(8000),@P100 varchar(8000),@P101 varchar(8000),@P102 varchar(8000),
@P103 varchar(8000),@P104 varchar(8000),@P105 varchar(8000),@P106 varchar(8000),@P107 varchar(8000),
@P108 varchar(8000),@P109 varchar(8000),@P110 varchar(8000),@P111 varchar(8000),@P112 varchar(8000),
@P113 varchar(8000),@P114 varchar(8000),@P115 varchar(8000),@P116 varchar(8000),@P117 varchar(8000),
@P118 varchar(8000),@P119 varchar(8000),@P120 varchar(8000),@P121 varchar(8000),@P122 varchar(8000),
@P123 varchar(8000),@P124 varchar(8000),@P125 varchar(8000),@P126 varchar(8000),@P127 varchar(8000),
@P128 varchar(8000),@P129 varchar(8000),@P130 varchar(8000),@P131 varchar(8000),@P132 varchar(8000),
@P133 varchar(8000),@P134 varchar(8000),@P135 varchar(8000),@P136 varchar(8000),@P137 varchar(8000),
@P138 varchar(8000),@P139 varchar(8000),@P140 varchar(8000),@P141 varchar(8000),@P142 varchar(8000),
@P143 varchar(8000),@P144 varchar(8000),@P145 varchar(8000),@P146 varchar(8000),@P147 varchar(8000),@P148 varchar(8000),
@P149 varchar(8000),@P150 varchar(8000),@P151 varchar(8000),@P152 varchar(8000),@P153 varchar(8000),@P154 varchar(8000),
@P155 varchar(8000),@P156 varchar(8000),@P157 varchar(8000),@P158 varchar(8000),@P159 varchar(8000),@P160 varchar(8000),
@P161 varchar(8000),@P162 varchar(8000),@P163 varchar(8000),@P164 varchar(8000),@P165 varchar(8000),@P166 varchar(8000),@P167 varchar(8000),@P168 varchar(8000),@P169 varchar(8000),
@P170 varchar(8000),@P171 varchar(8000),@P172 varchar(8000),@P173 varchar(8000),@P174 varchar(8000),@P175 varchar(8000),@P176 varchar(8000),@P177 varchar(8000),@P178 varchar(8000),
@P179 varchar(8000),@P180 varchar(8000),@P181 varchar(8000),@P182 varchar(8000),@P183 varchar(8000),@P184 varchar(8000),@P185 varchar(8000),@P186 varchar(8000),@P187 varchar(8000),
@P188 varchar(8000),@P189 varchar(8000),@P190 varchar(8000),@P191 varchar(8000),@P192 varchar(8000),@P193 varchar(8000),@P194 varchar(8000),@P195 varchar(8000)',
N'SELECT ENTITY_ID, NM, FORMAT, INTEGRITY, TEXT, REFERENCE, ALG, LANG, COMP, CHARSET, PAR_ID, UPDTSEQ, CONF
FROM w_entity_document_test_new
WHERE ENTITY_ID
IN
( @P0,@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,
@P17,@P18,@P19,@P20,@P21,@P22,@P23,@P24,@P25,@P26,@P27,@P28,@P29,@P30,@P31,@P32,@P33,@P34,@P35,@P36,@P37,@P38,@P39,@P40,@P41,@P42,@P43,@P44,@P45,@P46,@P47,@P48,@P49,@P50,@P51,@P52,@P53,@P54,@P55,@P56,@P57,@P58,@P59,@P60,@P61,@P62,@P63,@P64,@P65,@P66,@P67,@P68,@P69,@P70,@P71,@P72,@P73,@P74,@P75,@P76,@P77,@P78,@P79,@P80,@P81,@P82,@P83,@P84,@P85,@P86,@P87,@P88,@P89,@P90,@P91,@P92,@P93,@P94,@P95,@P96,@P97,@P98,@P99,@P100,@P101,@P102,@P103,@P104,@P105,@P106,@P107,@P108,@P109,@P110,@P111,@P112,@P113,@P114,@P115,@P116,@P117,@P118,@P119,@P120,@P121,@P122,@P123,@P124,@P125,@P126,@P127,@P128,@P129,@P130,@P131,@P132,@P133,@P134,@P135,@P136,@P137,@P138,@P139,@P140,@P141,@P142,@P143,@P144,@P145,@P146,@P147,@P148,@P149,@P150,@P151,@P152,@P153,@P154,@P155,@P156,@P157,@P158,@P159,@P160,@P161,@P162,@P163,@P164,@P165,@P166,@P167,@P168,@P169,@P170,@P171,@P172,@P173,@P174,@P175,@P176,@P177,@P178,@P179,@P180,@P181,@P182,@P183,@P184,@P185,@P186,@P187,@P188,@P189,@P190,@P191,@P192)

AND ENTITY_TYP_CD=@P193 AND DOC_TYP_CD = @P194 AND STS_CD = @p195
PLEASE HELP
Thanks
Lowell
Lowell
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75892 Visits: 41000
can you show us all the indexes on w_entity_document_test_new and the actual execution plan?

did your covering index include all teh columns in the query?
ie

CREATE INDEX IX_tezt
ON w_entity_document_test_new (ENTITY_ID,
ENTITY_TYP_CD,
DOC_TYP_CD,
STS_CD)
INCLUDE( NM,
FORMAT,
INTEGRITY,
TEXT,
REFERENCE,
ALG,
LANG,
COMP,
CHARSET,
PAR_ID,
UPDTSEQ,
CONF )



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65492 Visits: 17980
It looks like that is a great candidate for converting to a table parameter. It would certainly make this a lot easier to maintain.


CREATE TYPE VarChar8K as Table
(
Col1 varchar(8000) null
)



Then in your calling application you just need to populate your type instead on 192 variables. Your query is a lot simpler after that.


exec sp_prepexec @p1 output,N'@P1 VarChar8K,@P193 varchar(8000),@P194 varchar(8000),@P195 varchar(8000)',
N'SELECT ENTITY_ID, NM, FORMAT, INTEGRITY, TEXT, REFERENCE, ALG, LANG, COMP, CHARSET, PAR_ID, UPDTSEQ, CONF
FROM w_entity_document_test_new n
INNER JOIN @p1 p1 on n.ENTITY_ID = p1.Col1
WHERE ENTITY_TYP_CD=@P193 AND DOC_TYP_CD = @P194 AND STS_CD = @p195



You also have the advantage that if you want to have more or less values you either add them or don't add them to your table. You are no longer stuck with a fixed number of possibilities.

As for the performance, the method I mentioned would be a huge difference in the query so if you go this route I would just get it working first and then evaluate the performance. It is likely that the indexing scheme would be somewhat different for this type of approach.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ekant_alone
ekant_alone
Mr or Mrs. 500
Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)

Group: General Forum Members
Points: 542 Visits: 473
Thank you so much.
I just realized that i wasnt covering the index properly. Seems like no more look up... no high performance gain but no more key look ups.
I need to fig out how to fix the performance.
Thanks a lot....
ekant_alone
ekant_alone
Mr or Mrs. 500
Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)

Group: General Forum Members
Points: 542 Visits: 473
thanks,
I thought of using table values param. However, This would need to change application code and its not possible at this point in time... (time constraints ) . But i'll use this solution for future...
Thanks a ton!!
ekant_alone
ekant_alone
Mr or Mrs. 500
Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)

Group: General Forum Members
Points: 542 Visits: 473
quick question:
The above query is taking 5 secs to fetch 21 records in 150k records table. Is that speed ok??
Lowell
Lowell
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75892 Visits: 41000
we'd need to see the actual execution plan to see if you can make it faster;
I think what Sean Lange referred to, you want to try and convert the IN({200 parameters}) to a table you can join against instead;

i'm sure that is a performance hit, because the optimizer cannot use an index to search those parameters, or use statistics to know how unique they are. an in(defined list) gets converted into 200 OR statements, which requires a scan of all 150 values, instead of using an index to limit the results for you.

WHERE ENTITY_ID
IN (P0...P200)



if you could pre-populate the data into a temp table or table variable or something, i'd expect a performance boost.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search