January 7, 2013 at 7:38 am
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
January 7, 2013 at 7:47 am
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
January 7, 2013 at 8:12 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 7, 2013 at 8:30 am
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....
January 7, 2013 at 8:44 am
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!!
January 8, 2013 at 11:22 am
quick question:
The above query is taking 5 secs to fetch 21 records in 150k records table. Is that speed ok??
January 8, 2013 at 11:30 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply