We help IT Professionals succeed at work.

Select SQL: In clause

borama
borama asked
on
Hello experts,

database: SQL Server, Oracle 10g

I wonder whether my SQL below has a huge performance impact on query performace, i mean when the number of value in the IN clause ranges from several thousand:

----------------------------------------------
select fieldName from tableName where field2 in (1,2,3,4,...)
----------------------------------------------

When i need to select rows from a table constraint by a variety of value from a single column, can you recommend what is the  best way to achieve with regard to query performance?


Thanks,

borama
Comment
Watch Question

Commented:
Use Joins in this case it is faster and better.

Author

Commented:
I think you don't get what I am looking for.

Ex: table1
--------
Field1
-------
A
B
C
D
E
F
G
H
-------

select field1 from table1 where field1 in (A,B,C)
Use a temporary table, and insert there all the values that you are using with IN clause. Do a inner join with the temp table instead of using IN.

Commented:
how do you generate (A,B,C) if it will have a several thousand in it? You use some script or a DB query?

Commented:
Andrei74 have a good idea.
Temporary tables are not efficient at all.

Lee
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Temporary tables are a complete waste of time here.
The IN clause is as good as it gets, SQL goes directly to the index to SEEK all those values.

Here's a long winded example to illustrate.
The temp table goes through extra work to put it in there, the merge/nested loop join will have to do the same seek ops.
create table wellindexed(i int primary key)
insert wellindexed select 1
declare @i int
set @i=1
while @i < 18 begin
	insert wellindexed
	select i+(Select MAX(i) from wellindexed)
	from wellindexed;
	set @i=@i+1
end
GO
select COUNT(*) from wellindexed
-- shows that there are now 131072 records
GO
select *
into #tmp
from wellindexed
where i in (
3,6,9,12,15,18,21,24,27,30,33,36,39,42,45,48,51,54,57,60,63,66,69,72,75,
78,81,84,87,90,93,96,99,102,105,108,111,114,117,120,123,126,129,132,135,
138,141,144,147,150,153,156,159,162,165,168,171,174,177,180,183,186,189,
192,195,198,201,204,207,210,213,216,219,222,225,228,231,234,237,240,243,
246,249,252,255,258,261,264,267,270,273,276,279,282,285,288,291,294,297,
300,303,306,309,312,315,318,321,324,327,330,333,336,339,342,345,348,351,
354,357,360,363,366,369,372,375,378,381,384,387,390,393,396,399,402,405,
408,411,414,417,420,423,426,429,432,435,438,441,444,447,450,453,456,459,
462,465,468,471,474,477,480,483,486,489,492,495,498,501,504,507,510,513,
516,519,522,525,528,531,534,537,540,543,546,549,552,555,558,561,564,567,
570,573,576,579,582,585,588,591,594,597,600,603,606,609,612,615,618,621,
624,627,630,633,636,639,642,645,648,651,654,657,660,663,666,669,672,675,
678,681,684,687,690,693,696,699,702,705,708,711,714,717,720,723,726,729,
732,735,738,741,744,747,750,753,756,759,762,765,768,771,774,777,780,783,
786,789,792,795,798,801,804,807,810,813,816,819,822,825,828,831,834,837,
840,843,846,849,852,855,858,861,864,867,870,873,876,879,882,885,888,891,
894,897,900,903,906,909,912,915,918,921,924,927,930,933,936,939,942,945,
948,951,954,957,960,963,966,969,972,975,978,981,984,987,990,993,996,999,
1002,1005,1008,1011,1014,1017,1020,1023,1026,1029,1032,1035,1038,1041,1044,
1047,1050,1053,1056,1059,1062,1065,1068,1071,1074,1077,1080,1083,1086,1089,
1092,1095,1098,1101,1104,1107,1110,1113,1116,1119,1122,1125,1128,1131,1134,
1137,1140,1143,1146,1149,1152,1155,1158,1161,1164,1167,1170,1173,1176,1179,
1182,1185,1188,1191,1194,1197,1200,1203,1206,1209,1212,1215,1218,1221,1224,
1227,1230,1233,1236,1239,1242,1245,1248,1251,1254,1257,1260,1263,1266,1269,
1272,1275,1278,1281,1284,1287,1290,1293,1296,1299,1302,1305,1308,1311,1314,
1317,1320,1323,1326,1329,1332,1335,1338,1341,1344,1347,1350,1353,1356,1359,
1362,1365,1368,1371,1374,1377,1380,1383,1386,1389,1392,1395,1398,1401,1404,
1407,1410,1413,1416,1419,1422,1425,1428,1431,1434,1437,1440,1443,1446,1449,
1452,1455,1458,1461,1464,1467,1470,1473,1476,1479,1482,1485,1488,1491,1494,
1497,1500,1503,1506,1509,1512,1515,1518,1521,1524,1527,1530,1533,1536,1539,
1542,1545,1548,1551,1554,1557,1560,1563,1566,1569,1572,1575,1578,1581,1584,
1587,1590,1593,1596,1599,1602,1605,1608,1611,1614,1617,1620,1623,1626,1629,
1632,1635,1638,1641,1644,1647,1650,1653,1656,1659,1662,1665,1668,1671,1674,
1677,1680,1683,1686,1689,1692,1695,1698,1701,1704,1707,1710,1713,1716,1719,
1722,1725,1728,1731,1734,1737,1740,1743,1746,1749,1752,1755,1758,1761,1764,
1767,1770,1773,1776,1779,1782,1785,1788,1791,1794,1797,1800,1803,1806,1809,
1812,1815,1818,1821,1824,1827,1830,1833,1836,1839,1842,1845,1848,1851,1854,
1857,1860,1863,1866,1869,1872,1875,1878,1881,1884,1887,1890,1893,1896,1899,
1902,1905,1908,1911,1914,1917,1920,1923,1926,1929,1932,1935,1938,1941,1944,
1947,1950,1953,1956,1959,1962,1965,1968,1971,1974,1977,1980,1983,1986,1989,
1992,1995,1998,2001,2004,2007,2010,2013,2016,2019,2022,2025,2028,2031,2034,
2037,2040,2043,2046,2049,2052,2055,2058,2061,2064,2067,2070,2073,2076,2079,
2082,2085,2088,2091,2094,2097,2100,2103,2106,2109,2112,2115,2118,2121,2124,
2127,2130,2133,2136,2139,2142,2145,2148,2151,2154,2157,2160,2163,2166,2169,
2172,2175,2178,2181,2184,2187,2190,2193,2196,2199,2202,2205,2208,2211,2214,
2217,2220,2223,2226,2229,2232,2235,2238,2241,2244,2247,2250,2253,2256,2259,
2262,2265,2268,2271,2274,2277,2280,2283,2286,2289,2292,2295,2298,2301,2304,
2307,2310,2313,2316,2319,2322,2325,2328,2331,2334,2337,2340,2343,2346,2349,
2352,2355,2358,2361,2364,2367,2370,2373,2376,2379,2382,2385,2388,2391,2394,
2397,2400,2403,2406,2409,2412,2415,2418,2421,2424,2427,2430,2433,2436,2439,
2442,2445,2448,2451,2454,2457,2460,2463,2466,2469,2472,2475,2478,2481,2484,
2487,2490,2493,2496,2499,2502,2505,2508,2511,2514,2517,2520,2523,2526,2529,
2532,2535,2538,2541,2544,2547,2550,2553,2556,2559,2562,2565,2568,2571,2574,
2577,2580,2583,2586,2589,2592,2595,2598,2601,2604,2607,2610,2613,2616,2619,
2622,2625,2628,2631,2634,2637,2640,2643,2646,2649,2652,2655,2658,2661,2664,
2667,2670,2673,2676,2679,2682,2685,2688,2691,2694,2697,2700,2703,2706,2709,
2712,2715,2718,2721,2724,2727,2730,2733,2736,2739,2742,2745,2748,2751,2754,
2757,2760,2763,2766,2769,2772,2775,2778,2781,2784,2787,2790,2793,2796,2799,
2802,2805,2808,2811,2814,2817,2820,2823,2826,2829,2832,2835,2838,2841,2844,
2847,2850,2853,2856,2859,2862,2865,2868,2871,2874,2877,2880,2883,2886,2889,
2892,2895,2898,2901,2904,2907,2910,2913,2916,2919,2922,2925,2928,2931,2934,
2937,2940,2943,2946,2949,2952,2955,2958,2961,2964,2967,2970,2973,2976,2979,
2982,2985,2988,2991,2994,2997,3000,3003,3006,3009,3012,3015,3018,3021,3024,
3027,3030,3033,3036,3039,3042,3045,3048,3051,3054,3057,3060,3063,3066,3069,
3072,3075,3078,3081,3084,3087,3090,3093,3096,3099,3102,3105,3108,3111,3114,
3117,3120,3123,3126,3129,3132,3135,3138,3141,3144,3147,3150,3153,3156,3159,
3162,3165,3168,3171,3174,3177,3180,3183,3186,3189,3192,3195,3198,3201,3204,
3207,3210,3213,3216,3219,3222,3225,3228,3231,3234,3237,3240,3243,3246,3249,
3252,3255,3258,3261,3264,3267,3270,3273,3276,3279,3282,3285,3288,3291,3294,
3297,3300,3303,3306,3309,3312,3315,3318,3321,3324,3327,3330,3333,3336,3339,
3342,3345,3348,3351,3354,3357,3360,3363,3366,3369,3372,3375,3378,3381,3384,
3387,3390,3393,3396,3399,3402,3405,3408,3411,3414,3417,3420,3423,3426,3429,
3432,3435,3438,3441,3444,3447,3450,3453,3456,3459,3462,3465,3468,3471,3474,
3477,3480,3483,3486,3489,3492,3495,3498,3501,3504,3507,3510,3513,3516,3519,
3522,3525,3528,3531,3534,3537,3540,3543,3546,3549,3552,3555,3558,3561,3564,
3567,3570,3573,3576,3579,3582,3585,3588,3591,3594,3597,3600,3603,3606,3609,
3612,3615,3618,3621,3624,3627,3630,3633,3636,3639,3642,3645,3648,3651,3654,
3657,3660,3663,3666,3669,3672,3675,3678,3681,3684,3687,3690,3693,3696,3699,
3702,3705,3708,3711,3714,3717,3720,3723,3726,3729,3732,3735,3738,3741,3744,
3747,3750,3753,3756,3759,3762,3765,3768,3771,3774,3777,3780,3783,3786,3789,
3792,3795,3798,3801,3804,3807,3810,3813,3816,3819,3822,3825,3828,3831,3834,
3837,3840,3843,3846,3849,3852,3855,3858,3861,3864,3867,3870,3873,3876,3879,
3882,3885,3888,3891,3894,3897,3900,3903,3906,3909,3912,3915,3918,3921,3924,
3927,3930,3933,3936,3939,3942,3945,3948,3951,3954,3957,3960,3963,3966,3969,
3972,3975,3978,3981,3984,3987,3990,3993,3996,3999,4002,4005,4008,4011,4014,
4017,4020,4023,4026,4029,4032,4035,4038,4041,4044,4047,4050,4053,4056,4059,
4062,4065,4068,4071,4074,4077,4080,4083,4086,4089,4092,4095,4098,4101,4104,
4107,4110,4113,4116,4119,4122,4125,4128,4131,4134,4137,4140,4143,4146,4149,
4152,4155,4158,4161,4164,4167,4170,4173,4176,4179,4182,4185,4188,4191,4194,
4197,4200,4203,4206,4209,4212,4215,4218,4221,4224,4227,4230,4233,4236,4239,
4242,4245,4248,4251,4254,4257,4260,4263,4266,4269,4272,4275,4278,4281,4284,
4287,4290,4293,4296,4299,4302,4305,4308,4311,4314,4317,4320,4323,4326,4329,
4332,4335,4338,4341,4344,4347,4350,4353,4356,4359,4362,4365,4368,4371,4374,
4377,4380,4383,4386,4389,4392,4395,4398,4401,4404,4407,4410,4413,4416,4419,
4422,4425,4428,4431,4434,4437,4440,4443,4446,4449,4452,4455,4458,4461,4464,
4467,4470,4473,4476,4479,4482,4485,4488,4491,4494,4497,4500,4503,4506,4509,
4512,4515,4518,4521,4524,4527,4530,4533,4536,4539,4542,4545,4548,4551,4554,
4557,4560,4563,4566,4569,4572,4575,4578,4581,4584,4587,4590,4593,4596,4599,
4602,4605,4608,4611,4614,4617,4620,4623,4626,4629,4632,4635,4638,4641,4644,
4647,4650,4653,4656,4659,4662,4665,4668,4671,4674,4677,4680,4683,4686,4689,
4692,4695,4698,4701,4704,4707,4710,4713,4716,4719,4722,4725,4728,4731,4734,
4737,4740,4743,4746,4749,4752,4755,4758,4761,4764,4767,4770,4773,4776,4779,
4782,4785,4788,4791,4794,4797,4800,4803,4806,4809,4812,4815,4818,4821,4824,
4827,4830,4833,4836,4839,4842,4845,4848,4851,4854,4857,4860,4863,4866,4869,
4872,4875,4878,4881,4884,4887,4890,4893,4896,4899,4902,4905,4908,4911,4914,
4917,4920,4923,4926,4929,4932,4935,4938,4941,4944,4947,4950,4953,4956,4959,
4962,4965,4968,4971,4974,4977,4980,4983,4986,4989,4992,4995,4998,5001,5004,
5007,5010,5013,5016,5019,5022,5025,5028,5031,5034,5037,5040,5043,5046,5049,
5052,5055,5058,5061,5064,5067,5070,5073,5076,5079,5082,5085,5088,5091,5094,
5097,5100,5103,5106,5109,5112,5115,5118,5121,5124,5127,5130,5133,5136,5139,
5142,5145,5148,5151,5154,5157,5160,5163,5166,5169,5172,5175,5178,5181,5184,
5187,5190,5193,5196,5199,5202,5205,5208,5211,5214,5217,5220,5223,5226,5229,
5232,5235,5238,5241,5244,5247,5250,5253,5256,5259,5262,5265,5268,5271,5274,
5277,5280,5283,5286,5289,5292,5295,5298,5301,5304,5307,5310,5313,5316,5319,
5322,5325,5328,5331,5334,5337,5340,5343,5346,5349,5352,5355,5358,5361,5364,
5367,5370,5373,5376,5379,5382,5385,5388,5391,5394,5397,5400,5403,5406,5409,
5412,5415,5418,5421,5424,5427,5430,5433,5436,5439,5442,5445,5448,5451,5454,
5457,5460,5463,5466,5469,5472,5475,5478,5481,5484,5487,5490,5493,5496,5499,
5502,5505,5508,5511,5514,5517,5520,5523,5526,5529,5532,5535,5538,5541,5544,
5547,5550,5553,5556,5559,5562,5565,5568,5571,5574,5577,5580,5583,5586,5589,
5592,5595,5598,5601,5604,5607,5610,5613,5616,5619,5622,5625,5628,5631,5634,
5637,5640,5643,5646,5649,5652,5655,5658,5661,5664,5667,5670,5673,5676,5679,
5682,5685,5688,5691,5694,5697,5700,5703,5706,5709,5712,5715,5718,5721,5724,
5727,5730,5733,5736,5739,5742,5745,5748,5751,5754,5757,5760,5763,5766,5769,
5772,5775,5778,5781,5784,5787,5790,5793,5796,5799,5802,5805,5808,5811,5814,
5817,5820,5823,5826,5829,5832,5835,5838,5841,5844,5847,5850,5853,5856,5859,
5862,5865,5868,5871,5874,5877,5880,5883,5886,5889,5892,5895,5898,5901,5904,
5907,5910,5913,5916,5919,5922,5925,5928,5931,5934,5937,5940,5943,5946,5949,
5952,5955,5958,5961,5964,5967,5970,5973,5976,5979,5982,5985,5988,5991,5994,
5997,6000,6003,6006,6009,6012,6015,6018,6021,6024,6027,6030,6033,6036,6039,
6042,6045,6048,6051,6054,6057,6060,6063,6066,6069,6072,6075,6078,6081,6084,
6087,6090,6093,6096,6099,6102,6105,6108,6111,6114,6117,6120,6123,6126,6129,
6132,6135,6138,6141,6144,6147,6150,6153,6156,6159,6162,6165,6168,6171,6174,
6177,6180,6183,6186,6189,6192,6195,6198,6201,6204,6207,6210,6213,6216,6219,
6222,6225,6228,6231,6234,6237,6240,6243,6246,6249,6252,6255,6258,6261,6264,
6267,6270,6273,6276,6279,6282,6285,6288,6291,6294,6297,6300,6303,6306,6309,
6312,6315,6318,6321,6324,6327,6330,6333,6336,6339,6342,6345,6348,6351,6354,
6357,6360,6363,6366,6369,6372,6375,6378,6381,6384,6387,6390,6393,6396,6399,
6402,6405,6408,6411,6414,6417,6420,6423,6426,6429,6432,6435,6438,6441,6444,
6447,6450,6453,6456,6459,6462,6465,6468,6471,6474,6477,6480,6483,6486,6489,
6492,6495,6498,6501,6504,6507,6510,6513,6516,6519,6522,6525,6528,6531,6534,
6537,6540,6543,6546,6549,6552,6555,6558,6561,6564,6567,6570,6573,6576,6579,
6582,6585,6588,6591,6594,6597)
GO
set statistics io on
GO
select * from wellindexed
where i in (select i from #tmp)
GO
select *
from wellindexed
where i in (
3,6,9,12,15,18,21,24,27,30,33,36,39,42,45,48,51,54,57,60,63,66,69,72,75,
78,81,84,87,90,93,96,99,102,105,108,111,114,117,120,123,126,129,132,135,
138,141,144,147,150,153,156,159,162,165,168,171,174,177,180,183,186,189,
192,195,198,201,204,207,210,213,216,219,222,225,228,231,234,237,240,243,
246,249,252,255,258,261,264,267,270,273,276,279,282,285,288,291,294,297,
300,303,306,309,312,315,318,321,324,327,330,333,336,339,342,345,348,351,
354,357,360,363,366,369,372,375,378,381,384,387,390,393,396,399,402,405,
408,411,414,417,420,423,426,429,432,435,438,441,444,447,450,453,456,459,
462,465,468,471,474,477,480,483,486,489,492,495,498,501,504,507,510,513,
516,519,522,525,528,531,534,537,540,543,546,549,552,555,558,561,564,567,
570,573,576,579,582,585,588,591,594,597,600,603,606,609,612,615,618,621,
624,627,630,633,636,639,642,645,648,651,654,657,660,663,666,669,672,675,
678,681,684,687,690,693,696,699,702,705,708,711,714,717,720,723,726,729,
732,735,738,741,744,747,750,753,756,759,762,765,768,771,774,777,780,783,
786,789,792,795,798,801,804,807,810,813,816,819,822,825,828,831,834,837,
840,843,846,849,852,855,858,861,864,867,870,873,876,879,882,885,888,891,
894,897,900,903,906,909,912,915,918,921,924,927,930,933,936,939,942,945,
948,951,954,957,960,963,966,969,972,975,978,981,984,987,990,993,996,999,
1002,1005,1008,1011,1014,1017,1020,1023,1026,1029,1032,1035,1038,1041,1044,
1047,1050,1053,1056,1059,1062,1065,1068,1071,1074,1077,1080,1083,1086,1089,
1092,1095,1098,1101,1104,1107,1110,1113,1116,1119,1122,1125,1128,1131,1134,
1137,1140,1143,1146,1149,1152,1155,1158,1161,1164,1167,1170,1173,1176,1179,
1182,1185,1188,1191,1194,1197,1200,1203,1206,1209,1212,1215,1218,1221,1224,
1227,1230,1233,1236,1239,1242,1245,1248,1251,1254,1257,1260,1263,1266,1269,
1272,1275,1278,1281,1284,1287,1290,1293,1296,1299,1302,1305,1308,1311,1314,
1317,1320,1323,1326,1329,1332,1335,1338,1341,1344,1347,1350,1353,1356,1359,
1362,1365,1368,1371,1374,1377,1380,1383,1386,1389,1392,1395,1398,1401,1404,
1407,1410,1413,1416,1419,1422,1425,1428,1431,1434,1437,1440,1443,1446,1449,
1452,1455,1458,1461,1464,1467,1470,1473,1476,1479,1482,1485,1488,1491,1494,
1497,1500,1503,1506,1509,1512,1515,1518,1521,1524,1527,1530,1533,1536,1539,
1542,1545,1548,1551,1554,1557,1560,1563,1566,1569,1572,1575,1578,1581,1584,
1587,1590,1593,1596,1599,1602,1605,1608,1611,1614,1617,1620,1623,1626,1629,
1632,1635,1638,1641,1644,1647,1650,1653,1656,1659,1662,1665,1668,1671,1674,
1677,1680,1683,1686,1689,1692,1695,1698,1701,1704,1707,1710,1713,1716,1719,
1722,1725,1728,1731,1734,1737,1740,1743,1746,1749,1752,1755,1758,1761,1764,
1767,1770,1773,1776,1779,1782,1785,1788,1791,1794,1797,1800,1803,1806,1809,
1812,1815,1818,1821,1824,1827,1830,1833,1836,1839,1842,1845,1848,1851,1854,
1857,1860,1863,1866,1869,1872,1875,1878,1881,1884,1887,1890,1893,1896,1899,
1902,1905,1908,1911,1914,1917,1920,1923,1926,1929,1932,1935,1938,1941,1944,
1947,1950,1953,1956,1959,1962,1965,1968,1971,1974,1977,1980,1983,1986,1989,
1992,1995,1998,2001,2004,2007,2010,2013,2016,2019,2022,2025,2028,2031,2034,
2037,2040,2043,2046,2049,2052,2055,2058,2061,2064,2067,2070,2073,2076,2079,
2082,2085,2088,2091,2094,2097,2100,2103,2106,2109,2112,2115,2118,2121,2124,
2127,2130,2133,2136,2139,2142,2145,2148,2151,2154,2157,2160,2163,2166,2169,
2172,2175,2178,2181,2184,2187,2190,2193,2196,2199,2202,2205,2208,2211,2214,
2217,2220,2223,2226,2229,2232,2235,2238,2241,2244,2247,2250,2253,2256,2259,
2262,2265,2268,2271,2274,2277,2280,2283,2286,2289,2292,2295,2298,2301,2304,
2307,2310,2313,2316,2319,2322,2325,2328,2331,2334,2337,2340,2343,2346,2349,
2352,2355,2358,2361,2364,2367,2370,2373,2376,2379,2382,2385,2388,2391,2394,
2397,2400,2403,2406,2409,2412,2415,2418,2421,2424,2427,2430,2433,2436,2439,
2442,2445,2448,2451,2454,2457,2460,2463,2466,2469,2472,2475,2478,2481,2484,
2487,2490,2493,2496,2499,2502,2505,2508,2511,2514,2517,2520,2523,2526,2529,
2532,2535,2538,2541,2544,2547,2550,2553,2556,2559,2562,2565,2568,2571,2574,
2577,2580,2583,2586,2589,2592,2595,2598,2601,2604,2607,2610,2613,2616,2619,
2622,2625,2628,2631,2634,2637,2640,2643,2646,2649,2652,2655,2658,2661,2664,
2667,2670,2673,2676,2679,2682,2685,2688,2691,2694,2697,2700,2703,2706,2709,
2712,2715,2718,2721,2724,2727,2730,2733,2736,2739,2742,2745,2748,2751,2754,
2757,2760,2763,2766,2769,2772,2775,2778,2781,2784,2787,2790,2793,2796,2799,
2802,2805,2808,2811,2814,2817,2820,2823,2826,2829,2832,2835,2838,2841,2844,
2847,2850,2853,2856,2859,2862,2865,2868,2871,2874,2877,2880,2883,2886,2889,
2892,2895,2898,2901,2904,2907,2910,2913,2916,2919,2922,2925,2928,2931,2934,
2937,2940,2943,2946,2949,2952,2955,2958,2961,2964,2967,2970,2973,2976,2979,
2982,2985,2988,2991,2994,2997,3000,3003,3006,3009,3012,3015,3018,3021,3024,
3027,3030,3033,3036,3039,3042,3045,3048,3051,3054,3057,3060,3063,3066,3069,
3072,3075,3078,3081,3084,3087,3090,3093,3096,3099,3102,3105,3108,3111,3114,
3117,3120,3123,3126,3129,3132,3135,3138,3141,3144,3147,3150,3153,3156,3159,
3162,3165,3168,3171,3174,3177,3180,3183,3186,3189,3192,3195,3198,3201,3204,
3207,3210,3213,3216,3219,3222,3225,3228,3231,3234,3237,3240,3243,3246,3249,
3252,3255,3258,3261,3264,3267,3270,3273,3276,3279,3282,3285,3288,3291,3294,
3297,3300,3303,3306,3309,3312,3315,3318,3321,3324,3327,3330,3333,3336,3339,
3342,3345,3348,3351,3354,3357,3360,3363,3366,3369,3372,3375,3378,3381,3384,
3387,3390,3393,3396,3399,3402,3405,3408,3411,3414,3417,3420,3423,3426,3429,
3432,3435,3438,3441,3444,3447,3450,3453,3456,3459,3462,3465,3468,3471,3474,
3477,3480,3483,3486,3489,3492,3495,3498,3501,3504,3507,3510,3513,3516,3519,
3522,3525,3528,3531,3534,3537,3540,3543,3546,3549,3552,3555,3558,3561,3564,
3567,3570,3573,3576,3579,3582,3585,3588,3591,3594,3597,3600,3603,3606,3609,
3612,3615,3618,3621,3624,3627,3630,3633,3636,3639,3642,3645,3648,3651,3654,
3657,3660,3663,3666,3669,3672,3675,3678,3681,3684,3687,3690,3693,3696,3699,
3702,3705,3708,3711,3714,3717,3720,3723,3726,3729,3732,3735,3738,3741,3744,
3747,3750,3753,3756,3759,3762,3765,3768,3771,3774,3777,3780,3783,3786,3789,
3792,3795,3798,3801,3804,3807,3810,3813,3816,3819,3822,3825,3828,3831,3834,
3837,3840,3843,3846,3849,3852,3855,3858,3861,3864,3867,3870,3873,3876,3879,
3882,3885,3888,3891,3894,3897,3900,3903,3906,3909,3912,3915,3918,3921,3924,
3927,3930,3933,3936,3939,3942,3945,3948,3951,3954,3957,3960,3963,3966,3969,
3972,3975,3978,3981,3984,3987,3990,3993,3996,3999,4002,4005,4008,4011,4014,
4017,4020,4023,4026,4029,4032,4035,4038,4041,4044,4047,4050,4053,4056,4059,
4062,4065,4068,4071,4074,4077,4080,4083,4086,4089,4092,4095,4098,4101,4104,
4107,4110,4113,4116,4119,4122,4125,4128,4131,4134,4137,4140,4143,4146,4149,
4152,4155,4158,4161,4164,4167,4170,4173,4176,4179,4182,4185,4188,4191,4194,
4197,4200,4203,4206,4209,4212,4215,4218,4221,4224,4227,4230,4233,4236,4239,
4242,4245,4248,4251,4254,4257,4260,4263,4266,4269,4272,4275,4278,4281,4284,
4287,4290,4293,4296,4299,4302,4305,4308,4311,4314,4317,4320,4323,4326,4329,
4332,4335,4338,4341,4344,4347,4350,4353,4356,4359,4362,4365,4368,4371,4374,
4377,4380,4383,4386,4389,4392,4395,4398,4401,4404,4407,4410,4413,4416,4419,
4422,4425,4428,4431,4434,4437,4440,4443,4446,4449,4452,4455,4458,4461,4464,
4467,4470,4473,4476,4479,4482,4485,4488,4491,4494,4497,4500,4503,4506,4509,
4512,4515,4518,4521,4524,4527,4530,4533,4536,4539,4542,4545,4548,4551,4554,
4557,4560,4563,4566,4569,4572,4575,4578,4581,4584,4587,4590,4593,4596,4599,
4602,4605,4608,4611,4614,4617,4620,4623,4626,4629,4632,4635,4638,4641,4644,
4647,4650,4653,4656,4659,4662,4665,4668,4671,4674,4677,4680,4683,4686,4689,
4692,4695,4698,4701,4704,4707,4710,4713,4716,4719,4722,4725,4728,4731,4734,
4737,4740,4743,4746,4749,4752,4755,4758,4761,4764,4767,4770,4773,4776,4779,
4782,4785,4788,4791,4794,4797,4800,4803,4806,4809,4812,4815,4818,4821,4824,
4827,4830,4833,4836,4839,4842,4845,4848,4851,4854,4857,4860,4863,4866,4869,
4872,4875,4878,4881,4884,4887,4890,4893,4896,4899,4902,4905,4908,4911,4914,
4917,4920,4923,4926,4929,4932,4935,4938,4941,4944,4947,4950,4953,4956,4959,
4962,4965,4968,4971,4974,4977,4980,4983,4986,4989,4992,4995,4998,5001,5004,
5007,5010,5013,5016,5019,5022,5025,5028,5031,5034,5037,5040,5043,5046,5049,
5052,5055,5058,5061,5064,5067,5070,5073,5076,5079,5082,5085,5088,5091,5094,
5097,5100,5103,5106,5109,5112,5115,5118,5121,5124,5127,5130,5133,5136,5139,
5142,5145,5148,5151,5154,5157,5160,5163,5166,5169,5172,5175,5178,5181,5184,
5187,5190,5193,5196,5199,5202,5205,5208,5211,5214,5217,5220,5223,5226,5229,
5232,5235,5238,5241,5244,5247,5250,5253,5256,5259,5262,5265,5268,5271,5274,
5277,5280,5283,5286,5289,5292,5295,5298,5301,5304,5307,5310,5313,5316,5319,
5322,5325,5328,5331,5334,5337,5340,5343,5346,5349,5352,5355,5358,5361,5364,
5367,5370,5373,5376,5379,5382,5385,5388,5391,5394,5397,5400,5403,5406,5409,
5412,5415,5418,5421,5424,5427,5430,5433,5436,5439,5442,5445,5448,5451,5454,
5457,5460,5463,5466,5469,5472,5475,5478,5481,5484,5487,5490,5493,5496,5499,
5502,5505,5508,5511,5514,5517,5520,5523,5526,5529,5532,5535,5538,5541,5544,
5547,5550,5553,5556,5559,5562,5565,5568,5571,5574,5577,5580,5583,5586,5589,
5592,5595,5598,5601,5604,5607,5610,5613,5616,5619,5622,5625,5628,5631,5634,
5637,5640,5643,5646,5649,5652,5655,5658,5661,5664,5667,5670,5673,5676,5679,
5682,5685,5688,5691,5694,5697,5700,5703,5706,5709,5712,5715,5718,5721,5724,
5727,5730,5733,5736,5739,5742,5745,5748,5751,5754,5757,5760,5763,5766,5769,
5772,5775,5778,5781,5784,5787,5790,5793,5796,5799,5802,5805,5808,5811,5814,
5817,5820,5823,5826,5829,5832,5835,5838,5841,5844,5847,5850,5853,5856,5859,
5862,5865,5868,5871,5874,5877,5880,5883,5886,5889,5892,5895,5898,5901,5904,
5907,5910,5913,5916,5919,5922,5925,5928,5931,5934,5937,5940,5943,5946,5949,
5952,5955,5958,5961,5964,5967,5970,5973,5976,5979,5982,5985,5988,5991,5994,
5997,6000,6003,6006,6009,6012,6015,6018,6021,6024,6027,6030,6033,6036,6039,
6042,6045,6048,6051,6054,6057,6060,6063,6066,6069,6072,6075,6078,6081,6084,
6087,6090,6093,6096,6099,6102,6105,6108,6111,6114,6117,6120,6123,6126,6129,
6132,6135,6138,6141,6144,6147,6150,6153,6156,6159,6162,6165,6168,6171,6174,
6177,6180,6183,6186,6189,6192,6195,6198,6201,6204,6207,6210,6213,6216,6219,
6222,6225,6228,6231,6234,6237,6240,6243,6246,6249,6252,6255,6258,6261,6264,
6267,6270,6273,6276,6279,6282,6285,6288,6291,6294,6297,6300,6303,6306,6309,
6312,6315,6318,6321,6324,6327,6330,6333,6336,6339,6342,6345,6348,6351,6354,
6357,6360,6363,6366,6369,6372,6375,6378,6381,6384,6387,6390,6393,6396,6399,
6402,6405,6408,6411,6414,6417,6420,6423,6426,6429,6432,6435,6438,6441,6444,
6447,6450,6453,6456,6459,6462,6465,6468,6471,6474,6477,6480,6483,6486,6489,
6492,6495,6498,6501,6504,6507,6510,6513,6516,6519,6522,6525,6528,6531,6534,
6537,6540,6543,6546,6549,6552,6555,6558,6561,6564,6567,6570,6573,6576,6579,
6582,6585,6588,6591,6594,6597)
GO

Open in new window

Read the question carefully: "IN clause ranges from several thousand".
Temp table ideally must be populated from a single command (not as in example above) something like: insert into temp table select value from...etc
Hi,
If i remember well, in Oracle you have a maximum of 1000 values in the IN clause. And it will be long to parse and will take a lot of space in shared pool.
temporary table is the right way.
Regards,
Franck.

Author

Commented:
Thank everyone for your comments.

So in the end which one is faster "temporary table" or "IN clause". I want to add further information:from one query execution to another, the number of and actual value for the in clause will always vary. so if i opt for temporary table, then this table have to be repopulated for each and every execution of the query.

Do you think this is a good idea?


Thanks again.
borama
For small sets IN is perfect. For a large set, a temporary tables used properly, can significantly speed up the process. Try the other approach and let us know what is the difference.
Not clear to me if the question is related to Oracle or SQL Server.
Geert GOracle dba
Top Expert 2009

Commented:
you could use a ordinary table too

you could use it a as selection group

table selection
  selection_id number;
  field number;

if someone wants to save a selection you can do that too

selection_id = sequence for every new selection

select fieldname t
from table t, selection s
where t.field = s.field
  and s.selection_id = :selection_id

you can also edit your selection afterwards ... add / delete entries in the selection table
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
>> Temp table ideally must be populated from a single command (not as in example above) something like: insert into temp table select value from...etc

I don't believe you even understand the code properly to make such a comment.
The population isn't even the issue (and it is populated in one go).
I like to test things and give proper proof rather than go by some village rumour.

Look at just the execution plan of the last 2 queries.  No difference in speed.  Temp tables however well you design or execute it is just wasting time, on Sql Server I am 100% sure, on Oracle, 98%.
Geert GOracle dba
Top Expert 2009

Commented:
oh ... and your query doesn't change
and you don't keep creating new queries which need to be parsed, a execution plan generated ... etc
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Everyone seems to have skipped over a very important comment by franckpachot in http:#a32968787.

Oracle has a hard limit of 1000 entries in an in-list.  

If you have more than that you will need to write code to break them up and dynamically create multiple lists and use an OR:

select ...
from ...
where field2 in (1,2,3 ...) or field2 in (1001,1002,...) or field2 in (2001,...)...

Create an Oracle Global Temporary Table and join it with the main table.
@slightwv - Excellent point!

@cyberkiwi - Mr Guru, don't make wrong assumptions about what people understand or not, you might have surprises. It was indeed my fault by just looking at the top of your example where populating wellindexed  into a loop with 17 iteration.

Anyway, on a last note the performance for large IN list is the key. The problem is not query execution time, but query compile time - that explains why Oracle put that 1000 entries limit!

Logical conclusion is that by putting the values into a table or temporary table or whatever you like is certainly a good idea.

Geert GOracle dba
Top Expert 2009

Commented:
there is also a limit on the script length
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
I am extremely interested in hearing about the source or origins of that list of several thousand items in the "IN" clause - where is it coming from ?

Surely not user generated as in typed in, so, must be being produced somehow.

And however it is being produced, need to examine that process to see if it can be incorporated directly, or, via some other method (most likely inserting into a temp table).

Also, is this a query that will be run concurrently from a number of different requests ?
>> So in the end which one is faster "temporary table" or "IN clause".

Faster ? It depends. But for sure only the first one is scalable. When you use a database, a list of thousands of values should be in a table.

But mark_wills pointed the real question: where does this list come from ? Maybe you d'ont even need a temporary table.

Regards,
Franck.
Data Warehouse / Database Architect
Commented:
Hi borama,

I haven't seen a good description of how IN() works in this thread, so perhaps it's time one gets posted.

If you have a query that looks like this:

  SELECT * FROM mytable WHERE status_code IN (1,3,5,7,8,10)

The SQL is actually converted to:

  SELECT * FROM mytable WHERE status_code = 1 OR status_code = 3 OR status_code = 5
    OR status_code = 7 OR status_code = 8 OR status_code = 10


If the list of values in the IN predicate numbers into the 100s the SQL effectively has 100s of OR operators in the filter.  Would you write a query with 100s of OR operators?


Modern relational database engines are very good at joining tables.  It's the fundamental way that they work and are optimized for it.  In the queries shown so far in this discussion, it would be a rare set of circumstances where any significant query with a large number of values in the IN predicate out-performed an inner join.

Note that temporary tables come in two basic forms.  Declared and undeclared.  Undeclared temporary tables are often called derived tables.  The use of derived tables is incredibly common.  The result set of a query that is the result of a join operation is really a derived table.  Building an explicitly declared table might be overkill, but if you're going to use the same list of items several times it might give you a performance boost.


An inner joining of the temporary (derived) table with a permanent table is actually very efficient, especially if the permanent table has indexes on the join keys.  This is because the RDBMS logically swaps the two tables used in the operation.  They read the temporary table ONCE, joining the permanent table to it.  The result is that the temporary table is read once, and the indexes in the permanent table are used to effect the join, if possible.

If you use the IN predicate, the DBMS engine doesn't logically swap the tables.  The result is that for every row in the permanent table, the DBMS has to scan (potentially all of) the items in the IN list.

This is why join operations typically beat out the IN filter for performance.


Good Luck,
Kent

Author

Commented:
Thanks for the heated discussion.

About the question of the source of my variable list of value. To say it in a simple way,I have a table of user and another table of item. Each user has permission to view certain number of item in the item table. So when this user login, i want to show only those items that this user has be authorized. Do achieve this I can use a simple join between the 2 two tables, that's it.

But in my real situation, it's more complicated. The user table is not directly assigned permission to the item table. Instead, there are group table, and company table. A user may belong to one or more groups and a group may belong to one or more company. Permission will be set on group or company which will ultimately delegates to user.

So when a user login, i have to calculate authority for this user base on the membership to group and company. and then retrieve only the items from the item table that this user is authorized to.

Hope my explanation is clear enough for you to understand my problem.


Thanks,
have a good weekend.
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
It seems people like to deal with theory and emotions... and completely skim over the easy to use, ready to cut and paste proof of concept.

I'll go one step further and show you the query plan produced by Sql Server (2008) for the IN clause.
Re: http:#a32974193,
>> If you use the IN predicate, the DBMS engine doesn't logically swap the tables.
>> The result is that for every row in the permanent table, the DBMS has to scan
>> (potentially all of) the items in the IN list.
Obviously Sql Server is not one of the DBMS engines you are referring to.

I acquiesce on the Oracle debate.  The 1000 record limit really stinks, but I believe in the latest version it's gone up by one order of magnitude.  I also read that Oracle does a terrible job of optimizing IN clauses, seems like Sql Server (2008) has it beat in this department and is looking better every day.
ee.PNG
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
>> Thanks for the heated discussion

It has been a great discussion, "heated" normally implies argumentative and I am not sure it has been so "heated" as much as "impassioned"or "enthusiastic".

As it turns out it is a great question with a few differing thoughts, and some great expert involvement - well done borama for bringing it to us. Thank you.

>> when a user login, i have to calculate authority for this user base on the membership to group and company.

That does sound like we should be able to incorporate that into the query. Could you tell us how you calculate, and is part of the colculatoin some kind of query to retrieve those ID's ?


SQL Server and Oracle BOTH have limitations on a large number of items in the "IN" clause - certainly when they number in the several thousand. From SQL Server 2008 books online ( http://msdn.microsoft.com/en-us/library/ms177682.aspx ) we see the following caution :

Including an extremely large number of values (many thousands) in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table.

Error 8623:

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Error 8632:

Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.


So, sooner or later it would seem that you will run into some kind of difficulty - especially if the number of ID's is going to increase over time. Because of that, I would strongly recommend an approach that avoids the likelihood of running into those kinds of limits or constraints.

Certainly the use of a temp table would work, or, generating a VIEW over the user data to give the ID's (and then join the view), or somehow directly incorporating the user / group /company  relationship.

Other than that, you could use the "IN" clause however would need inspection of some kind to trap for very large lists that might compromise the success of the query, and in that regard, would not be using it if one of the other three methods can be made to work.

So, not much more I can add to the discussion unless we want to start looking at that calculation that you do for user / group / company.

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Sorry, there is one more thing.... if you do something like :

select fieldName from tableName where field2 in (select ID from my_user_group_company_data)

Then you can overcome some of those limits in both. But if you can express it that way, then would most likely be using join philosophies anyway.
Geert GOracle dba
Top Expert 2009

Commented:
i don't really know your table logic
but it seems to me that you want to keep your select on 2 tables

believe me oracle can deal with hard queries, very hard ones ..., a join on 2 (or 20 tables) doesn't really bother the dbms
it's built for that

just a quick thought:
table items: columns = (item_id, item_name)
table users: columns = (user_id, user_name)
table companies: columns (company_id, company_name)
table groups: columns (group_id, group_name)
table item_groups: columns (gi_id, item_id, group_id)
table company_groups (gc_id, group_id, company_id)
table user_groups(gu_id, group_id, user_id)
table item_company_groups(gi_id, company_id)
table user_company_groups(gu_id, company_id)

each item belongs to a item_groups
each company belongs to a company_groups
each user belongs to a user_groups
each item_groups belongs to a company
each user_groups belongs to a company

basically you need to work up to the top layer to see if the user can see something
select distinct i.item_name, c.company_name
from items i, item_groups ig, item_company_groups icg, companies c
where i.item_id = ig.item_id
  and exists (
    select 1 
    from users u, user_groups ug, user_company_group ucg   
    where u.user_name = :user_name
      and u.user_ud = ug.user_id
      and ug.gu_id = ucg.gu_id
      and icg.company_id = ucg.company_id)
  and ig.gi_id = icg.gi_id
  and icg.company_id = c.company_id

Open in new window

Kent OlsenData Warehouse / Database Architect

Commented:
Hi CyberKiwi,

That's cool.  I didn't know that SQL Server did that.

Do you know when they added that functionality?


Kent
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Hi Kent,

been there quite a while :) which bit in particular ?

Hi cyberkiwi,

That is the type of thing I was suggesting. Would still like to hear from borama first though... ideally we have "firmer" relationships for user + group + company

Author

Commented:
hi everyone,

a user belong to group -> group belong to company. permission on item table are assigned to group and company only. table structure below:

user table (userid, username)
group table (groupId, groupname)
company table (comId, comName)
user_group table (ugId, userid, groupid)
group_company table (gcId, groupid, comId)
item table table (itemId, itemName)
group_item table (giId, groupId, itemID)
company_item table (ciId, comId, itemID)
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Well,

this type of thing should work (we will call it the 'user' query):

select distinct u.userid, I.itemid, I.itemname
from user U
inner join user_group UG on u.userid = ug.userid
inner join group_company GC on ug.groupid = gc.groupid
inner join group_item GI on gc.groupid = gi.groupid
inner join company_item CI on gc.comid = ci.comid
inner join item I on I.itemid in (gi.itemid,ci.itemid)
where u.userid = 4

So, you could use the above 'user' query in an IN (select distinct I.itemid......etc.....) clause if you need to keep the same IN() clause - believe that overcomes the long list of literals

Or, create the above 'user' query as a view

create view vw_user_items as
select distinct u.userid, I.itemid, I.itemname
from user U
inner join user_group UG on u.userid = ug.userid
inner join group_company GC on ug.groupid = gc.groupid
inner join group_item GI on gc.groupid = gi.groupid
inner join company_item CI on gc.comid = ci.comid
inner join item I on I.itemid in (gi.itemid,ci.itemid)

then join using the view :

select *
from tablename t
inner join vw_user_items vui on vui.itemid = t.itemdid
where vui.userid = 4

Or, use the above 'user' query directly in the join

select distinct u.userid, I.itemid, I.itemname, t.*
from user U
inner join user_group UG on u.userid = ug.userid
inner join group_company GC on ug.groupid = gc.groupid
inner join group_item GI on gc.groupid = gi.groupid
inner join company_item CI on gc.comid = ci.comid
inner join item I on I.itemid in (gi.itemid,ci.itemid)
inner join tablename t I.itemid = t.itemdid
where u.userid = 4

Of course there are the group name and company name that I have excluded from the above, but they are just as easily joined in...

Hope that gives you a few more different ideas to think about.

Author

Commented:
Hi mark_wills, thanks so much.

Author

Commented:
Hi mark_wills, once again thank you and will follow your suggestion.

I'm curious to know what if there are more than 2 level of membership. at the moment, i only have group and company. what if I have 5 or 6 more level. for example: user -> group -> department -> business unit -> region, country -> company.

Do you think your solution still work well or there is another optimized way to do it?

Thanks,
borama
Geert GOracle dba
Top Expert 2009
Commented:
the databases shouldn't have any problem at all with a query like that
if the tables get big, then indexes can be created on the columns being used in the joins
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018
Commented:
Pretty much as Geert says.

That type of construct could support more layers, and you would need to make sure of indexes and so forth to ensure some kind of performance is retained.

There are other possible ways in which those queries could be expressed. For example that last one uses the "security" to then link to tablename, but in some situations it could be that tablename is used to link to the security (as in the view example). The most "correct" answer about performance is " it depends " on things like predicates, indexes, and other data relationships as to which approach will work best for you in any given circumstance.

Ideally, if that did grow in terms of complexity, you would really need to start examining a "security" matrix as seperate exercise and come up with an optimal solution based on volumes, spread of data (over key values), complexity of relationships.

Maybe the "view" approach is a good option if there is any likelihood of change because all you need to do is to incorporate the new layers within the view and might avoid having to change all the different places where that "security" is used.

If it is truly complex and the data relationships in the security matrix do not change all that often, then it might also be a reasonable approach to consider materialising that view - either adding indexes to the view itself, or, at runtime building a temp table. But they are significant steps, and might be a consideration during deliberations about your internal security paradigm.