超级复杂的查询条件,如何优化性能..........求求兄弟们了,高手们了!

Question

我有两张表,一张基本表:A,1000条记录; 一张数据表:B,5000多条记录...现在情况是这样子的,A有个字段,可以包含B表中的1-5000个信息的使用权(把对应的ID存在了A表的某字段中)有用需求必须要将A表中包含的N条B表的记录信息打印出来...由于页面量访问量非常大,又要分页,导致数据库CPU 100%,如果我将信息CACHE到缓存中的话,那WEB服务器CPU 100%具体SQL如下:
Select Top 20 * From B Where Tag=5 and (bl_ID In(2988,3059,3086,2463,2302,1611,2366,2231,2059,2058,2056,2054,3027,3021,3020,3019,3018,3017,3016,3003,3001,2948,2908,2891,2842,2817,2763,2727,2702,2664,2625,2325,2323,2295,2277,2275,2273,2252,2251,2250,2249,2248,2247,2206,2204,2201,2195,2153,2145,2138,2137,2135,2134,2132,2130,2129,2127,2123,2119,2117,2114,2109,2107,2106,1950,1949,1913,1911,1910,1909,1907,1906,1905,1903,1901,1900,1899,1898,1895,1883,1882,1881,1879,1871,1833,1831,1830,1829,1828,1827,1826,1825,1824,1821,1820,1817,1813,1808,1807,1805,1804,1803,1802,1801,1800,1799,1797,1795,1794,1793,1792,1791,1790,1789,1788,1786,1783,1781,1780,1769,1767,1751,1740,1731,1727,1722,1712,1706,1693,96,290,353,359,476,544,690,3034,3032,2927,2887,2881,2860,2822,2810,2766,2703,2695,2690,2667,2582,2565,2548,2525,2503,2499,2489,2483,2480,2478,2477,2476,2475,2470,2469,2468,2467,2448,2446,2443,2440,2439,2435,2429,2428,2421,2420,2419,2417,2416,2415,2412,2410,2347,2346,2338,2335,2334,2324,2322,2321,2316,2310,2308,2306,2305,2304,2301,2300,2298,2293,2291,2289,2288,2287,2285,2284,2283,2282,2281,2279,2278,2276,2271,2269,2266,2265,2264,2263,2262,2261,2243,2242,2241,2240,2229,2228,2227,2225,2224,2223,2222,2221,2207,2205,2202,2197,2175,2174,2173,2172,2171,2170,2169,2168,2167,2166,2165,2164,2162,2161,2160,2159,2158,2122,2120,2002,2001,2000,1999,1998,1997,1996,1995,1994,1993,1992,1976,1974,1973,1972,1951,1948,1946,1810,1703,1686,1685,1684,1683,1682,1674,1672,1671,1670,1669,1667,1662,1644,1643,1642,1641,1640,1639,1638,1637,1636,1635,1634,1627,1614,1613,1612,1610,1609,1607,1588,1587,1585,1574,1561,1560,1555,1544,1538,111,112,104,106,108,109,93,100,135,144,150,206,223,234,266,437,537,548,560,579,582,583,647,654,712,714,739,754,777,786,792,848,866,889,908,937,938,945,963,979,985,986,999,1014,1031,1032,1033,15,1069,1087,1110,1118,1135,1136,1144,1147,1148,1150,1164,1179,1192,1193,1198,1199,1204,1206,1223,1224,1227,1232,1247,1249,1262,1342,1387,1419,3035,3030,3029,3028,3015,3014,3013,3012,3011,3010,3008,3007,3002,2992,2983,2965,2961,2945,2942,2941,2940,2939,2938,2937,2936,2935,2933,2932,2930,2929,2919,2882,2865,2852,2829,2823,2814,2661,2583,2567,2539,2497,2495,2405,2404,2403,2402,2401,2400,2399,2383,2376,2365,2364,2357,2356,2355,2354,2352,2345,2343,2329,2326,2319,2294,2280,2274,2272,2270,2268,2267,2237,2236,2235,2234,2233,2230,2215,2213,2212,2211,2210,2209,2203,2200,2199,2196,2192,2189,2188,2187,2185,2184,2183,2182,2180,2179,2178,2177,2152,2146,2143,2142,2141,2139,2136,2131,2128,2126,2125,2124,2116,2115,2112,2111,2105,2104,2102,2101,2100,2098,2097,2096,2095,2094,2093,2091,2090,2089,2088,2086,2085,2083,2082,2081,2080,2079,2078,2076,2074,2073,2072,2071,2070,2069,2068,2067,2065,2064,2063,2062,2061,2057,2055,2052,2051,2050,2048,2047,2046,2045,2044,2043,2042,2041,2040,2039,2038,2037,2036,2035,2034,2033,2032,2031,2030,2029,2028,2026,2025,2024,2023,2021,2020,2019,2018,2017,2016,2015,2010,2009,2008,2006,2005,2003,1918,1842,1839,1815,1759,1750,1749,1734,1709,1708,1679,1594,1522,57,58,59,60,19,12,136,141,215,220,417,524,623,624,625,626,700,716,749,750,914,931,1011,1125,1268,1269,3080,3081,3082,3079,3078,3070,3071,3072,3073,3074,3075,3076,3077,3063,3064,3065,3067,3068,3061,3060,3058,3062,2901,2573,3054,3055,3056,3053,3057,3004,3052,3051,3050,3048,3049,3047,3031,3046,3045,3044,3043,3042,3006,429,3041,3040,3039,3038,3037,3036,3033,3026,3025,3024,3023,3022,3005,2909,2729,2737,2655,2838))  Order By bl_ID Desc
搞得我头痛了个把月了,兄弟们啊,给点好的建议或解决办法吧.存储过程啊什么的,提点思路也行啊.

Answer 1

既然每次显示20个，为什么不直接在前面控制
比如：
Select * From B Where Tag=5 and (bl_ID In(2988,3059,3086,2463,2302,1611,2366,2231,2059,2058,2056，2054,3027,3021,3020,3019,3018,3017,3016)把取20个的任务放到取A库中去。

Answer 2

不要用IN查询，
可以考虑先创建一个临时表＃t（列bl_ID ),将那部分值传入到#t
再和B进行连接查询速度应该会有很大的改观。

Answer 3

假设你的a表中存放所有id的字段叫all_ID,则可以简化如下Select Top 20 * From B Where Tag=5 and exists(select * from a where b.bl_ID in(a.all_ID))  Order By bl_ID Desc在sql中尽量不要用in,如果一定要用的话,可以想着用exists来替换.以上语句你修改后执行一下,看是否是你想要的结果.

Answer 4

create table #t (bl_ID varchar(50))
insert into #t 
select bl_ID  from ASelect Top 20 * From B 
join #t on B.bl_ID=#t.bl_ID
Where Tag=5 
drop table #t//这里我没有考虑分页的问题，你自行研究

Answer 5

建议楼主重新设计一下A表.
以下是我的思路,主要设想是以空间换时间.
字段1   字段2
用户1   3026
用户1   3027
用户2   3026
用户2   3028SELECT Top 20 b.* 
FROM A表 a LEFT OUT JOIN B表 b ON a.字段2=b.bl_ID
WHERE a.字段1='用户1' AND b.tag=5;

Answer 6

yes, you can use
(1)set rowcount
(2)select top N
to pagin, key word "IN" is not recommended.

Answer 7

看你这个需求，似乎可以这样
假设 A库名字为 AAA，B 库名字为BBB
select top 20 b.userid,b.username.... from AAA a,BBB b where b.tag=5 and a.ID=b.BL_ID
不需要用 in

Answer 8

楼主可以在学SQL的基本查询语法:)

Answer 9

http://www.cnblogs.com/bonny.wong/archive/2005/01/21/95193.html今天看到这篇文章.好文~好好看下就明白了.

Answer 10

问题出在 order by
分两次
第二次 order by

Answer 11

问题出在 order by
分两次
第二次 得到记录后 order by 一次；

Answer 12

怎么没反应：select 字段1，字段2,...字段n from ...... insert into aaaselect top 20 字段1，字段2,...字段n from aaa  Order By bl_ID Desc

Answer 13

create table #t (bl_ID varchar(50))
insert into #t 
select bl_ID  from ASelect Top 20 * From B 
join #t on B.bl_ID=#t.bl_ID
Where Tag=5 把这个改为存储过程就ok了