Larde INLIST in a WHERE clause
-
Hello everyone,
In our app. we are using the IN operator with many values.
In fact we can have 1000 elements for the IN operator.
Is there a bunch of "good practices" using large INLIST with SQL Server?
In a particular case we are issuing about 100 qeries that have the same patern:
select * from table_a where col_1 IN (... 1000 elements ...)
For about the 50 first qeries, the response time is very good : around 200 mili-seconds (Duration in the SQL Profiler).
For the other 50 queries, the response time drop to around: 12000 mili-seconds (Duration in the SQL Profiler).
I still look at this problem but if you have any clue or similar past experience, please, just post it to let me know.
Regards,
Carl
-
Hi
You can use a derived table to get rid off the long IN clause.
Use http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=850 function as derived table and pass your in clause elements to this function and join with the main query.
here is the sample code
SELECT * FROM table_a e INNER JOIN dbo.Split('element1,element2,...,element100',',') d
ON
e.Col1 = d.Value
-
Thans a lot for your post.
I'll try it next week to see how the engine treat it and how it scales...
But I see a limit here:
Having a large INLIST often imply having to pass to the Split derived table a parameter larger than 8000...
E.g. having 999 element in the INLIST with 9 characters each result in a string of more than 8000.
In this case we would have to issue more than one query to do the job (more round trips to the DB server)
.Best reagrds,
Carl
-
Where do the values in the INLIST come from? You can build a derived table for your join without having to use dbo.split.
-
Hello John,
The values are dynamically chosen by the application user.
We are using ADO.NET.
Could you post an example on how we could build a derived table without using the split function?
Best regards,
Carl
-
Well, if you look at what dbo.split is doing, it is basically creating a table variable using the values that you pass in. Your concern is that your need to use 1000+ elements will overflow the input parameter for dbo.Split. You could just create yourself a table variable or temp table to hold the list elements. At this point, using the term derived table is not really accurate. What you would have is, instead of joining to a derived table, a join to a table variable or temp-table. Make sense?
For example (this is pseudo code, you'll need to write the t-sql code):
1. declare a table variable or temp table. Define the data type for your elements/values column appropriate to the data it will hold and, if possible, the same as the data type for the column that you will use to join it to.
2. INSERT INTO table variable or temp table the values from your application that would have been in your IN list.
3. Rewrite your query to SELECT e.* FROM table_a e INNER JOIN (TableVariable/TempTableName) d ON e.Col1 = d.Value
-
Hello John,
at my sense this technique could not scale because every row inserted in the temp table will involve a round-trip to the DB server...
1000 values = 1000 round-trips.
For the case I am working on, there is 100 queries involving 1000 element each... I would prefer 100 round-trips instead of 100 000!
Best regards,
Carl
-
The SPLIT function used with ADO.NET does not seem to work properly (n.b. I hardcoded the separator instead of passing it to the function):
exec sp_executesql N'SELECT * FROM hasogl.pellpasc.stop s INNER JOIN dbo.SPLIT(@0) d ON s.stop_id_1 = d.strval', N'@0 varchar(8000)', @0 = 'P165981, P165982, P165983, P165984, P165985, P165986, P165987, P165988, P165989, P16599, P165990, P165991, P165992, P165993, P165994, P165995, P165996, P165997, P165998, P165999, P166, P1660, P16600, P166000, P166001, P166002, P166003, P166004, P166005, P166006, P166007, P166008, P166009, P16601, P166010, P166011, P166012, P166013, P166014, P166015, P166016, P166017, P166018, P166019, P166020, P166021, P166022, P166023, P166024, P166025, P166026, P166027, P166028, P166029, P16603, P166030, P166031, P166032, P166033, P166034, P166035, P166036, P166037, P166038, P166039, P16604, P166040, P166041, P166042, P166043, P166044, P166045, P166046, P166047, P166048, P166049, P166050, P166051, P166052, P166053, P166054, P166055, P166056, P166057, P166058, P166059, P16606, P166060, P166061, P166062, P166063, P166064, P166065, P166066, P166067, P166068, P166069, P16607, P166070, P166071, P166072, P166073, P166074, P166075, P166076, P166077, P166078, P166079, P16608, P166080, P166081, P166082, P166083, P166084, P166085, P166086, P166087, P166088, P166089, P16609, P166090, P166091, P166092, P166093, P166094, P166095, P166096, P166097, P166098, P166099, P1661, P16610, P166100, P166101, P166102, P166103, P166104, P166105, P166106, P166107, P166108, P166109, P16611, P166110, P166111, P166112, P166113, P166114, P166115, P166116, P166117, P166118, P166119, P16612, P166120, P166121, P166122, P166123, P166124, P166125, P166126, P166127, P166128, P166129, P16613, P166130, P166131, P166132, P166133, P166134, P166135, P166136, P166137, P166138, P166139, P166140, P166141, P166142, P166143, P166144, P166145, P166146, P166147, P166148, P166149, P16615, P166150, P166151, P166152, P166153, P166154, P166155, P166156, P166157, P166158, P166159, P16616, P166160, P166161, P166162, P166163, P166164, P166165, P166166, P166167, P166168, P166169, P16617, P166170, P166171, P166172, P166173, P166174, P166175, P166176, P166177, P166178, P166179, P16618, P166180, P166181, P166182, P166183, P166184, P166185, P166186, P166187, P166188, P166189, P16619, P166190, P166191, P166192, P166193, P166194, P166195, P166196, P166197, P166198, P166199, P1662, P16620, P166200, P166201, P166202, P166203, P166204, P166205, P166206, P166207, P166208, P166209, P16621, P166210, P166211, P166212, P166213, P166214, P166215, P166216, P166217, P166218, P166219, P16622, P166220, P166221, P166222, P166223, P166224, P166225, P166226, P166227, P166228, P166229, P16623, P166230, P166231, P166232, P166233, P166234, P166235, P166236, P166237, P166238, P166239, P166240, P166241, P166242, P166243, P166244, P166245, P166246, P166247, P166248, P166249, P16625, P166250, P166251, P166252, P166253, P166254, P166255, P166256, P166257, P166258, P166259, P16626, P166260, P166261, P166262, P166263, P166264, P166265, P166266, P166267, P166268, P166269, P16627, P166270, P166271, P166272, P166273, P166274, P166275, P166276, P166277, P166278, P166279, P16628, P166280, P166281, P166282, P166283, P166284, P166285, P166286, P166287, P166288, P166289, P166290, P166291, P166292, P166293, P166294, P166295, P166296, P166297, P166298, P166299, P1663, P16630, P166300, P166301, P166302, P166303, P166304, P166305, P166306, P166307, P166308, P166309, P16631, P166310, P166311, P166312, P166313, P166314, P166315, P166316, P166317, P166318, P166319, P16632, P166320, P166321, P166322, P166323, P166324, P166325, P166326, P166327, P166328, P166329, P16633, P166330, P166331, P166332, P166333, P166334, P166335, P166336, P166337, P166338, P166339, P16634, P166340, P166341, P166342, P166343, P166344, P166345, P166346, P166347, P166348, P166349, P16635, P166350, P166351, P166352, P166353, P166354, P166355, P166356, P166357, P166358, P166359, P16636, P166360, P166361, P166362, P166363, P166364, P166365, P166366, P166367, P166368, P166369, P16637, P166370, P166371, P166372, P166373, P166374, P166375, P166376, P166377, P166378, P166379, P16638, P166380, P166381, P166382, P166383, P166384, P166385, P166386, P166387, P166388, P166389, P16639, P166390, P166391, P166392, P166393, P166394, P166395, P166396, P166397, P166398, P166399, P1664, P16640, P166400, P166401, P166402, P166403, P166404, P166405, P166406, P166407, P166408, P166409, P16641, P166410, P166411, P166412, P166413, P166414, P166415, P166416, P166417, P166418, P166419, P16642, P166420, P166421, P166422, P166423, P166424, P166425, P166426, P166427, P166428, P166429, P16643, P166430, P166431, P166432, P166433, P166434, P166435, P166436, P166437, P166438, P166439, P16644, P166440, P166441, P166442, P166443, P166444, P166445, P166446, P166447, P166448, P166449, P16645, P166450, P166451, P166452, P166453, P166454, P166455, P166456, P166457, P166458, P166459, P16646, P166460, P166461, P166462, P166463, P166464, P166465, P166466, P166467, P166468, P166469, P16647, P166470, P166471, P166472, P166473, P166474, P166475, P166476, P166477, P166478, P166479, P166480, P166481, P166482, P166483, P166484, P166485, P166486, P166487, P166488, P166489, P16649, P166490, P166491, P166492, P166493, P166494, P166495, P166496, P166497, P166498, P166499, P1665, P16650, P166500, P166501, P166502, P166503, P166504, P166505, P166506, P166507, P166508, P166509, P16651, P166510, P166511, P166512, P166513, P166514, P166515, P166516, P166517, P166518, P166519, P16652, P166520, P166521, P166522, P166523, P166524, P166525, P166526, P166527, P166528, P166529, P16653, P166530, P166531, P166532, P166533, P166534, P166535, P166536, P166537, P166538, P166539, P16654, P166540, P166541, P166542, P166543, P166544, P166545, P166546, P166547, P166548, P166549, P16655, P166550, P166551, P166552, P166553, P166554, P166555, P166556, P166557, P166558, P166559, P166560, P166561, P166562, P166563, P166564, P166565, P166566, P166567, P166568, P166569, P16657, P166570, P166571, P166572, P166573, P166574, P166575, P166576, P166577, P166578, P166579, P16658, P166580, P166581, P166582, P166583, P166584, P166585, P166586, P166587, P166588, P166589, P16659, P166590, P166591, P166592, P166593, P166594, P166595, P166596, P166597, P166598, P166599, P1666, P16660, P166600, P166601, P166602, P166603, P166604, P166605, P166606, P166607, P166608, P166609, P16661, P166610, P166611, P166612, P166613, P166614, P166615, P166616, P166617, P166618, P166619, P16662, P166620, P166621, P166622, P166623, P166624, P166625, P166626, P166627, P166628, P166629, P166630, P166631, P166632, P166633, P166634, P166635, P166636, P166637, P166638, P166639, P16664, P166640, P166641, P166642, P166643, P166644, P166645, P166646, P166647, P166648, P166649, P16665, P166650, P166651, P166652, P166653, P166654, P166655, P166656, P166657, P166658, P166659, P16666, P166660, P166661, P166662, P166663, P166664, P166665, P166666, P166667, P166668, P166669, P16667, P166670, P166671, P166672, P166673, P166674, P166675, P166676, P166677, P166678, P166679, P16668, P166680, P166681, P166682, P166683, P166684, P166685, P166686, P166687, P166688, P166689, P166690, P166691, P166692, P166693, P166694, P166695, P166696, P166697, P166698, P166699, P1667, P16670, P166700, P166701, P166702, P166703, P166704, P166705, P166706, P166707, P166708, P166709, P16671, P166710, P166711, P166712, P166713, P166714, P166715, P166716, P166717, P166718, P166719, P16672, P166720, P166721, P166722, P166723, P166724, P166725, P166726, P166727, P166728, P166729, P16673, P166730, P166731, P166732, P166733, P166734, P166735, P166736, P166737, P166738, P166739, P16674, P166740, P166741, P166742, P166743, P166744, P166745, P166746, P166747, P166748, P166749, P16675, P166750, P166751, P166752, P166753, P166754, P166755, P166756, P166757, P166758, P166759, P16676, P166760, P166761, P166762, P166763, P166764, P166765, P166766, P166767, P166768, P166769, P16677, P166770, P166771, P166772, P166773, P166774, P166775, P166776, P166777, P166778, P166779, P16678, P166780, P166781, P166782, P166783, P166784, P166785, P166786, P166787, P166788, P166789, P16679, P166790, P166791, P166792, P166793, P166794, P166795, P166796, P166797'
This return only one row and it is supposed to return 898 rows. I'll have to check that...
-
Then using dbo.split will not scale either. Whether you are using a table variable (as dbo.split is) or a temp table, you must insert your values into it. The table variable and temp table solutions will have the same amount of server round trips.
If your concern here is server rounds trips, keep using IN. If your concern is duration (as stated in your original post), then I recommend that you set up all three scenarios (one using IN, one using temp table, and one using table variable) and compare the performance in terms of duration, I/O, CPU cycles, etc.
-
Another thing to note is that inserting 1000 values does not necessarily mean 1000 round trips. Test this out in QA and see.
-
Hello John,
Hope you add a nice weekend.
You are right, I should (and I will test, test and retest) the three scenarios (something I began to put in place last week).
Best regards,
Carl
-
Thanks Barsuk.
Same kind of issue.
Regards.
Carl
-
Hello John,
Here is preliminary results:
For 898 element for the IN operator:
Using a derived table (SPLIT function):
Network Statistics
Number of server roundtrips 6
Number of TDS packets sent 6
Number of TDS packets received 45
Number of bytes sent 16430
Number of bytes received 174312
Time Statistics
Cumulative client processing time 2
Cumulative wait time on server replies 4
Using a temporary table:
Network Statistics
Number of server roundtrips 22
Number of TDS packets sent 22
Number of TDS packets received 414
Number of bytes sent 81404
Number of bytes received 1.68595e+006
Time Statistics
Cumulative client processing time 11
Cumulative wait time on server replies 22
Using a classical IN:
Network Statistics
Number of server roundtrips 20
Number of TDS packets sent 20
Number of TDS packets received 268
Number of bytes sent 73230
Number of bytes received 1.08857e+006
Time Statistics
Cumulative client processing time 10
Cumulative wait time on server replies 5
Regards
Carl
-
The problem was just that the SPLIT function cannot handle the space between each of the element passed to it:
This was ok: 'element1,element2,element3,...'
But not this: 'element1, element2, element3, ...'
I just add a REPLACE(..., ', ', ',').
And it works fine.
Thanks to you all.
Carl
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply