原因:
(select top 60000 base_id from cstest order by base_id asc)
是个临时表,临时表没有索引。修改方法:
select top 60000 base_id into #a from cstest order by base_id asc
create index ind_a_base_id on #a(base_id) select * from cstest where
base_id in
(select top 10 base_id from cstest where base_id in
#a
order by base_id desc)
order by base_id asc
(select top 60000 base_id from cstest order by base_id asc)
是个临时表,临时表没有索引。修改方法:
select top 60000 base_id into #a from cstest order by base_id asc
create index ind_a_base_id on #a(base_id) select * from cstest where
base_id in
(select top 10 base_id from cstest where base_id in
#a
order by base_id desc)
order by base_id asc
from
(select top 60000 base_id from cstest order by base_id asc)
order by base_id desc) A
order by base_id
from
(select top 60000 * from cstest order by base_id asc)
order by base_id desc) A
order by base_id
select top 10 * from cstest order by base_id asc
如不信你用查询分析器分析分析。如果真要取得效果只有Crazyfor 的写法是好的。
还是回应一下吧!
首先,
select top 10 * from cstest order by base_id asc
取的前10条记录,升序select * from cstest where
base_id in
(select top 10 base_id from cstest where base_id in
(select top 60000 base_id from cstest order by base_id asc)
order by base_id desc)
order by base_id asc
取的第59991 到第60000的10条记录,最后结果升序
其次,我的写错了:
select top 60000 base_id into #a from cstest order by base_id asc
create index ind_a_base_id on #a(base_id) select * from cstest where
base_id in
(select top 10 base_id from cstest where base_id in
(select base_id from #a)
order by base_id desc)
order by base_id asc再次,你测试过吗??top 60000,临时表空间多少?
base_id in (select base_id from #a order by base_id desc)
order by base_id asc
首先你说的“(select top 60000 base_id from cstest order by base_id asc)是个临时表,临时表没有索引。”不对,因为它在取这60000条记录的时候已经用上索引了。
另外我没有看清楚楼主的排序方式,是我不对,楼主是要59990-60000记录,所以简写是不对的。
你在将记录插入临时表的时候花去了近一半的时间占用。和取出这些记录的时间几乎是相等的。这难道不是浪费吗?如果你一意孤行的话,请把60000改为6000000,试一下就一清二楚了。
现在我没有办法和你争出是非,因为你没有观点,搂主说慢,你说一个能快的方法,再争也不迟。(你原来说的CrazyFor的方法被你自己否定了)
PIII900 128M
win2000+sql server2000企业版
table name :t_data
CLUSTERED PRIMARY KEY :id
Rows:441000语句1:
select * from t_data where
id in
(select top 10 id from t_data where id in
(select top 60000 id from t_data order by id asc)
order by id desc)
order by id asc时间:390ms语句2:
select * from t_data where
id in
(select top 10 id from t_data where id in
(select top 100000 id from t_data order by id asc)
order by id desc)
order by id asc时间:4s语句3:
select * from t_data where
id in
(select top 10 id from t_data where id in
(select top 150000 id from t_data order by id asc)
order by id desc)
order by id asc时间:5s语句4:
select * from t_data where
id in
(select top 10 id from t_data where id in
(select top 200000 id from t_data order by id asc)
order by id desc)
order by id asc时间:6s语句5:
select * from t_data where
id in
(select top 10 id from t_data where id in
(select top 250000 id from t_data order by id asc)
order by id desc)
order by id asc时间:过了15m没有出来,中断语句6:
select * from t_data where
id in
(select top 10 id from t_data where id in
(select top 300000 id from t_data order by id asc)
order by id desc)
order by id asc时间:过了20m没有出来,中断语句7:
select top 60000 id into #a from t_data order by id asccreate index ind_a_base_id on #a(id) select * from t_data where
id in (select top 10 id from #a order by id desc)
order by id asc时间:20s语句8:
select top 100000 id into #a from t_data order by id asccreate index ind_a_base_id on #a(id) select * from t_data where
id in (select top 10 id from #a order by id desc)
order by id asc时间:21s语句9:
select top 150000 id into #a from t_data order by id asccreate index ind_a_base_id on #a(id) select * from t_data where
id in (select top 10 id from #a order by id desc)
order by id asc时间:23s语句10:
select top 200000 id into #a from t_data order by id asccreate index ind_a_base_id on #a(id) select * from t_data where
id in (select top 10 id from #a order by id desc)
order by id asc时间:24s语句11:
select top 250000 id into #a from t_data order by id asccreate index ind_a_base_id on #a(id) select * from t_data where
id in (select top 10 id from #a order by id desc)
order by id asc时间:26s语句12:
select top 300000 id into #a from t_data order by id asccreate index ind_a_base_id on #a(id) select * from t_data where
id in (select top 10 id from #a order by id desc)
order by id asc时间:28s语句13:
select top 400000 id into #a from t_data order by id asccreate index ind_a_base_id on #a(id) select * from t_data where
id in (select top 10 id from #a order by id desc)
order by id asc时间:29s语句14:
select * from t_data where
id in
(select top 10 id from t_data where id in
(select top 250000 id from t_data order by id asc)
order by id desc)
order by id asc时间:过了25m没有出来,还没有中断
而楼主的服务器配置比我的还要低。楼主提问完后,就没有在露面了?原因分析就不贴了,明眼人都能看出来。
估计按缺省应该是聚集索引,聚集索引和非聚集索引的影响不大。
开始我确实未加聚集索引,60000条记录以下查询时不到1秒,60000条以上5分钟没有结果。后来建立了聚集索引,600000条记录以下查询时不到1秒,600000条以上有结果。cceo的方法我做了测试,可以800000条记录以下查询时不到1秒,1000000条记录用时14秒。
我发现cceo的方法还是挺快的。但下面语句是错的,系统提示“除非同时指定了 TOP,否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。”
select top 60000 base_id into #a from cstest order by base_id asc
create index ind_a_base_id on #a(base_id) select top 10 * from cstest where
base_id in (select base_id from #a order by base_id desc)
order by base_id asc应改为
select * from cstest where
base_id in (select top 10 base_id from #a order by base_id desc)
order by base_id asc不知对否?请多指教。另外问一下如何查看一个临时表的大小?先谢了。