Link to home
Start Free TrialLog in
Avatar of borama
boramaFlag for France

asked on

Select SQL: In clause

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
Avatar of NurAzije
NurAzije
Flag of Bosnia and Herzegovina image

Use Joins in this case it is faster and better.
Avatar of borama

ASKER

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.
how do you generate (A,B,C) if it will have a several thousand in it? You use some script or a DB query?
Andrei74 have a good idea.
Avatar of Lee
Temporary tables are not efficient at all.

Lee
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.
Avatar of borama

ASKER

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.
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
>> 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%.
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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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.

there is also a limit on the script length
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.
ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of borama

ASKER

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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>> 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.

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.
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

Hi CyberKiwi,

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

Do you know when they added that functionality?


Kent
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
Avatar of borama

ASKER

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)
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.
Avatar of borama

ASKER

Hi mark_wills, thanks so much.
Avatar of borama

ASKER

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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial