sql speed

[jda]

Hi Ruslan,

I have put together a method of joining cursors by storing recIDs of 
individual searches in arrays, and then either ANDing or ORing the 
arrays (in RB). I then do a final search using the recIDs that are in 
the final array. As a result, there can be thousands of numbers. I 
find that the search is actually pretty slow, no doubt due to the sql 
overhead. For the search below, for about 2800 recIDs, it takes a 
about 7 seconds. I wonder if there is something new in V2 that would 
speed this up (I still need a cursor at the end of the operation). 
Thanks.

Jon

select 
id,hit,uniqueID,authors,thedate,title,editors,authorsSortMethod from 
thereferences where RecID in 
(30,33,34,38,39,40,43,56,57,61,62,63,64,65,66,70,71,72,73,86,87,92,114,115,142,157,163,167,169,171,172,174,175,176,178,181,216,219,225,226,235,236,239,240,241,263,264,266,270,271,272,276,277,279,297,298,304,306,307,308,327,328,331,332,333,334,335,336,337,338,341,342,343,344,345,346,348,349,350,351,358,360,362,376,377,396,398,400,405,406,407,414,419,420,434,436,437,438,441,443,456,457,458,473,475,486,488,493,499,502,509,511,522,524,529,530,538,544,546,547,551,555,556,563,567,569,574,575,581,582,583,584,585,591,605,616,618,633,634,635,636,637,639,650,671,695,697,698,699,700,701,702,703,704,705,707,709,710,711,719,720,721,722,735,742,743,744,745,755,756,757,774,775,776,781,784,810,813,814,815,822,833,849,856,864,865,866,867,868,869,876,888,889,890,891,892,893,894,895,912,914,918,922,923,935,941,960,961,963,964,966,967,968,969,970,996,997,1022,1023,1024,1025,1026,1036,1037,1038,1043,1044,1045,1046,1047,1048,1049,1051,1052,1053,1054,1058,1059,1060,1061,1062,1063,1064,1065,1069,10!
 7 
0,1085,1089,1092,1093,1096,1097,1102,1103,1113,1114,1115,1116,1117,1118,1119,1132,1134,1136,1137,1138,1139,1140,1141,1142,1143,1144,1145,1146,1147,1148,1149,1150,1151,1152,1153,1154,1155,1156,1157,1158,1161,1162,1163,1164,1165,1193,1194,1196,1197,1198,1199,1201,1202,1203,1204,1205,1206,1207,1208,1210,1211,1212,1214,1215,1216,1226,1227,1228,1230,1257,1258,1259,1260,1306,1307,1327,1329,1337,1355,1361,1368,1386,1387,1390,1396,1403,1404,1405,1406,1407,1408,1409,1410,1411,1412,1413,1414,1415,1426,1433,1442,1443,1456,1466,1480,1482,1483,1484,1485,1486,1487,1488,1489,1490,1491,1492,1493,1494,1495,1500,1501,1504,1516,1519,1520,1527,1528,1530,1536,1542,1550,1551,1552,1553,1566,1574,1575,1576,1577,1587,1588,1589,1590,1597,1607,1615,1628,1639,1640,1641,1647,1664,1665,1666,1667,1668,1669,1672,1689,1701,1708,1711,1712,1714,1715,1716,1717,1719,1721,1722,1724,1726,1727,1728,1730,1731,1732,1733,1735,1749,1790,1793,1795,1798,1806,1808,1817,1826,1844,1853,1854,1855,1856,1892,1938,1940,1981,20!
 1 
0,2012,2013,2014,2024,2061,2094,2095,2096,2098,2099,2101,2102,2105,2110,2122,2123,2124,2126,2127,2141,2146,2152,2153,2154,2155,2183,2184,2187,2190,2191,2192,2193,2196,2197,2198,2199,2200,2201,2202,2205,2206,2208,2209,2210,2220,2228,2229,2230,2231,2232,2247,2248,2251,2252,2255,2269,2274,2276,2277,2286,2291,2293,2303,2306,2307,2308,2310,2311,2314,2333,2334,2336,2341,2343,2347,2348,2355,2361,2374,2375,2376,2377,2378,2384,2385,2386,2387,2394,2395,2403,2415,2416,2427,2428,2429,2446,2447,2448,2450,2451,2452,2454,2457,2461,2493,2523,2524,2538,2541,2542,2547,2558,2586,2587,2596,2597,2598,2599,2601,2602,2603,2632,2633,2634,2635,2636,2637,2638,2639,2662,2666,2676,2677,2678,2689,2691,2706,2707,2711,2715,2736,2747,2748,2751,2779,2795,2814,2815,2816,2834,2835,2836,2870,2880,2883,2886,2888,2901,2902,2923,2924,2925,2926,2927,2929,2930,2932,2941,2945,2947,2949,2950,2951,2952,2953,2954,2957,2958,2980,2984,3002,3003,3028,3036,3037,3038,3046,3055,3057,3098,3102,3103,3104,3105,3106,3107,3108,31!
 0 
9,3110,3111,3112,3115,3132,3137,3176,3177,3179,3183,3184,3185,3192,3193,3194,3196,3198,3199,3201,3202,3203,3210,3211,3212,3213,3214,3223,3234,3245,3246,3253,3254,3255,3256,3281,3291,3294,3299,3303,3315,3316,3317,3336,3348,3353,3354,3355,3378,3379,3390,3391,3392,3394,3396,3431,3443,3450,3451,3454,3457,3458,3459,3460,3461,3462,3463,3466,3469,3470,3489,3520,3524,3530,3625,3626,3627,3628,3630,3631,3632,3633,3634,3635,3636,3637,3638,3639,3640,3641,3642,3643,3649,3651,3653,3657,3658,3660,3661,3662,3664,3666,3667,3669,3713,3714,3718,3723,3733,3734,3743,3766,3767,3768,3774,3790,3793,3794,3809,3810,3812,3853,3878,3885,3886,3903,3904,3905,3906,3910,3915,3924,3927,3928,3937,3938,3940,3941,3950,3951,3958,3961,3962,3995,3996,4005,4006,4007,4012,4015,4041,4042,4050,4066,4069,4070,4071,4093,4105,4109,4110,4112,4113,4114,4115,4116,4117,4118,4119,4120,4121,4122,4123,4124,4125,4126,4127,4128,4129,4130,4133,4137,4141,4143,4182,4183,4184,4185,4188,4189,4192,4198,4199,4200,4204,4205,4206,4207,42!
 0 
8,4209,4227,4228,4231,4233,4236,4237,4242,4244,4245,4258,4260,4263,4264,4265,4266,4268,4269,4270,4271,4281,4282,4283,4284,4286,4287,4288,4289,4293,4295,4305,4306,4307,4308,4337,4338,4343,4347,4348,4349,4351,4352,4363,4364,4366,4367,4371,4372,4379,4384,4385,4386,4389,4390,4391,4392,4393,4394,4395,4396,4408,4409,4420,4421,4462,4463,4464,4465,4466,4470,4477,4479,4480,4481,4495,4497,4498,4509,4522,4523,4524,4525,4526,4527,4535,4536,4542,4557,4558,4567,4570,4574,4575,4586,4588,4589,4594,4599,4610,4611,4612,4613,4651,4657,4664,4665,4686,4743,4744,4752,4803,4810,4816,4818,4819,4823,4828,4829,4843,4844,4855,4856,4877,4878,4879,4883,4884,4885,4886,4894,4895,4897,4898,4915,4918,4919,4920,4921,4936,4944,4949,4965,4977,5001,5006,5023,5026,5027,5033,5034,5047,5061,5080,5084,5085,5106,5116,5118,5124,5129,5159,5166,5181,5182,5201,5204,5205,5206,5207,5208,5209,5210,5211,5212,5213,5214,5215,5216,5217,5218,5219,5226,5229,5246,5247,5251,5253,5262,5264,5269,5273,5274,5301,5302,5303,5304,5305,53!
 0 
7,5308,5310,5313,5314,5321,5322,5330,5331,5332,5333,5334,5342,5343,5344,5348,5349,5350,5358,5366,5374,5388,5396,5398,5400,5402,5405,5407,5408,5417,5421,5438,5439,5440,5441,5442,5460,5461,5469,5482,5503,5530,5531,5532,5533,5534,5535,5536,5537,5539,5541,5542,5544,5545,5546,5548,5551,5552,5553,5554,5556,5561,5562,5577,5579,5590,5591,5593,5594,5595,5596,5597,5598,5599,5600,5601,5602,5603,5646,5647,5650,5651,5652,5654,5658,5659,5670,5676,5681,5682,5683,5684,5689,5693,5699,5704,5707,5723,5725,5733,5767,5782,5791,5792,5797,5818,5819,5822,5823,5825,5826,5827,5828,5829,5830,5832,5833,5835,5836,5837,5838,5839,5840,5841,5842,5845,5846,5847,5849,5857,5858,5859,5861,5864,5866,5867,5869,5870,5871,5872,5873,5874,5875,5876,5878,5879,5880,5881,5882,5883,5884,5885,5886,5887,5888,5889,5890,5891,5892,5893,5894,5895,5896,5897,5898,5899,5900,5901,5902,5903,5906,5907,5916,5917,5918,5919,5920,5921,5922,5923,5924,5925,5926,5928,5929,5930,5932,5934,5935,5936,5937,5938,5939,5940,5941,5943,5944,5959,60!
 0 
7,6011,6012,6017,6024,6025,6026,6028,6031,6032,6033,6037,6054,6070,6071,6077,6113,6116,6117,6118,6123,6124,6144,6149,6150,6151,6152,6153,6158,6166,6167,6172,6174,6175,6176,6193,6197,6202,6203,6204,6205,6218,6244,6266,6281,6282,6284,6286,6287,6288,6289,6290,6300,6305,6307,6308,6313,6314,6319,6324,6325,6326,6327,6328,6329,6330,6337,6338,6339,6340,6341,6342,6343,6344,6345,6346,6348,6349,6350,6351,6352,6353,6354,6355,6356,6357,6358,6359,6361,6371,6394,6423,6431,6474,6475,6481,6487,6494,6495,6517,6518,6519,6520,6521,6522,6523,6524,6525,6526,6527,6547,6548,6549,6551,6555,6558,6563,6567,6568,6569,6570,6572,6573,6574,6575,6576,6586,6587,6610,6611,6641,6645,6646,6651,6676,6677,6691,6692,6695,6704,6712,6740,6747,6748,6749,6750,6751,6752,6753,6756,6771,6772,6773,6774,6776,6777,6778,6779,6785,6786,6788,6789,6804,6814,6828,6830,6831,6832,6833,6834,6842,6843,6844,6845,6846,6847,6848,6851,6853,6854,6855,6856,6858,6877,6897,6906,6907,6909,6917,6918,6929,6930,6931,6932,6935,6936,6940,6941,69!
 5 
2,6958,6962,6986,7008,7009,7010,7011,7013,7014,7015,7016,7017,7018,7019,7024,7025,7043,7055,7056,7063,7066,7070,7078,7090,7111,7127,7128,7129,7130,7132,7133,7134,7135,7149,7152,7155,7171,7177,7178,7180,7212,7228,7230,7241,7248,7281,7282,7290,7292,7293,7294,7295,7312,7321,7322,7330,7332,7333,7334,7335,7336,7337,7339,7342,7346,7347,7355,7360,7361,7365,7367,7368,7382,7384,7400,7401,7402,7403,7415,7420,7421,7422,7423,7424,7428,7460,7470,7472,7478,7479,7480,7485,7488,7489,7490,7491,7492,7493,7494,7495,7496,7497,7498,7499,7520,7533,7534,7535,7537,7539,7541,7547,7549,7550,7552,7595,7596,7597,7598,7599,7604,7607,7610,7611,7612,7613,7614,7616,7620,7621,7626,7627,7628,7629,7633,7634,7641,7642,7643,7644,7647,7653,7658,7661,7662,7663,7674,7676,7677,7679,7680,7681,7683,7684,7685,7686,7687,7688,7689,7690,7691,7692,7693,7700,7701,7702,7709,7710,7711,7712,7713,7714,7715,7716,7744,7767,7772,7773,7788,7794,7797,7812,7815,7817,7828,7829,7830,7831,7832,7833,7834,7838,7840,7857,7864,7865,7883,78!
 8 
4,7885,7886,7890,7898,7929,7931,7932,7933,7954,7957,7968,7969,7993,8002,8006,8007,8028,8029,8030,8044,8045,8055,8056,8061,8082,8083,8084,8085,8086,8087,8088,8089,8093,8094,8097,8105,8108,8109,8115,8118,8119,8120,8121,8122,8124,8129,8133,8146,8149,8154,8164,8168,8169,8170,8171,8172,8173,8187,8188,8189,8190,8191,8205,8206,8207,8208,8221,8259,8260,8261,8262,8274,8275,8279,8280,8281,8282,8285,8286,8306,8307,8317,8324,8326,8328,8341,8349,8350,8351,8352,8355,8359,8360,8391,8393,8394,8395,8404,8405,8413,8414,8415,8416,8418,8419,8420,8421,8422,8427,8428,8448,8449,8456,8475,8478,8479,8480,8481,8482,8483,8484,8485,8487,8488,8489,8491,8492,8493,8494,8495,8503,8504,8505,8519,8520,8521,8522,8523,8524,8525,8526,8527,8528,8529,8530,8531,8532,8533,8534,8535,8536,8537,8538,8539,8540,8541,8542,8543,8544,8545,8546,8547,8548,8549,8551,8553,8584,8592,8593,8607,8631,8633,8634,8635,8640,8644,8651,8652,8653,8678,8706,8717,8719,8723,8724,8728,8729,8731,8755,8756,8757,8758,8759,8760,8761,8762,8763,87!
 6 
4,8765,8767,8781,8786,8787,8788,8789,8791,8801,8805,8808,8809,8810,8813,8827,8839,8851,8893,8894,8895,8896,8897,8898,8899,8954,8955,8956,8957,8958,8959,8960,8961,8962,8963,8964,8980,8983,8984,8985,8986,8990,8991,9001,9038,9039,9056,9061,9062,9066,9067,9068,9069,9070,9071,9072,9073,9074,9075,9090,9092,9111,9113,9114,9115,9116,9138,9154,9159,9163,9165,9166,9167,9168,9170,9171,9172,9173,9174,9175,9177,9187,9188,9189,9190,9191,9192,9193,9199,9201,9217,9218,9219,9220,9221,9257,9267,9268,9269,9270,9272,9273,9274,9275,9276,9277,9278,9279,9280,9281,9282,9283,9286,9287,9291,9292,9293,9301,9305,9307,9308,9319,9320,9321,9322,9323,9324,9325,9326,9327,9335,9337,9338,9339,9340,9346,9361,9362,9370,9381,9398,9425,9427,9429,9433,9442,9443,9444,9445,9446,9452,9454,9455,9456,9457,9458,9459,9460,9462,9463,9471,9474,9475,9476,9477,9478,9482,9483,9490,9492,9493,9498,9511,9512,9513,9514,9515,9523,9529,9530,9531,9534,9536,9541,9542,9544,9545,9546,9547,9551,9553,9554,9557,9560,9561,9562,9581,9582,95!
 8 
8,9590,9593,9596,9597,9601,9618,9625,9626,9627,9652,9667,9702,9703,9718,9719,9720,9721,9722,9743,9747,9792,9793,9794,9795,9796,9797,9798,9799,9800,9801,9802,9803,9804,9805,9806,9807,9808,9809,9810,9811,9812,9813,9828,9829,9831,9832,9833,9834,9835,9836,9837,9838,9839,9840,9843,9849,9854,9855,9856,9857,9858,9859,9860,9861,9862,9863,9864,9865,9866,9868,9869,9870,9871,9872,9873,9874,9878,9880,9881,9882,9883,9884,9885,9892,9901,9904,9908,9913,9917,9931,9933,9934,9935,9948,9955,9992,10001,10009,10015,10022,10023,10026,10027,10036,10043,10044,10045,10050,10052,10053,10109,10110,10111,10112,10116,10133,10134,10135,10137,10142,10143,10144,10145,10146,10147,10148,10149,10150,10151,10153,10154,10155,10159,10160,10161,10162,10163,10164,10180,10181,10182,10183,10185,10186,10188,10189,10190,10191,10192,10193,10197,10199,10202,10207,10211,10212,10217,10218,10220,10221,10223,10224,10225,10226,10228,10229,10230,10231,10233,10235,10270,10277,10278,10279,10282,10290,10304,10305,10306,10316,103!
 2 
4,10325,10326,10327,10328,10329,10330,10331,10332,10333,10334,10335,10336,10337,10338,10339,10341,10342,10343,10344,10345,10346,10347,10348,10349,10350,10351,10352,10353,10354,10355,10356,10357,10358,10380,10402,10403,10419,10420,10422,10424,10441,10455,10460,10468,10472,10478,10513,10514,10515,10518,10523,10524,10525,10532,10549,10552,10559,10604,10607,10624,10625,10656,10657,10659,10661,10662,10664,10665,10667,10669,10672,10673,10675,10684,10715,10723,10724,10725,10726,10727,10728,10729,10735,10739,10740,10745,10750,10753,10757,10759,10770,10771,10772,10773,10774,10776,10789,10831,10832,10834,10840,10843,10847,10848,10854,10859,10861,10864,10948,10949,10951,10956,10957,10958,10959,10960,10961,10962,10963,10990,10991,10992,10993,10994,11028,11032,11033,11038,11040,11048,11049,11053,11054,11055,11058,11061,11066,11086,11087,11093,11094,11095,11096,11097,11098,11099,11101,11103,11119,11121,11147,11153,11154,11160,11161,11162,11163,11164,11165,11166,11169,11170,11171,11210,112!
 2 
2,11223,11224,11225,11226,11230,11268,11270,11271,11274,11287,11299,11300,11301,11302,11316,11317,11325,11327,11333,11340,11344,11345,11349,11401) 
ORDER BY authorsSortMethod