select
top 10 *
from
dbo.tbRunRecord runrecord0_
where(runrecord0_.MACID ='1001' or runrecord0_.MACID ='333')
order by
runrecord0_.BH DESC 耗时3分38秒多谁能帮我把时间缩短到30秒?
已经在MACID上面建立非聚集索引(desc),这个表里面有4700万条记录。
MACID可能有更多的值,目前先测试两个
还有
--row方案select *
from (
select row_number()over(order by bh desc)rn,*
from (
select top 10000000 0 tc,* from tbrunrecord runrecord0_
where
runrecord0_.MACID ='234'
)t
)tt
where rn>10000000-10
耗时2分27秒
如何缩短到30秒内?
top 10 *
from
dbo.tbRunRecord runrecord0_
where(runrecord0_.MACID ='1001' or runrecord0_.MACID ='333')
order by
runrecord0_.BH DESC 耗时3分38秒多谁能帮我把时间缩短到30秒?
已经在MACID上面建立非聚集索引(desc),这个表里面有4700万条记录。
MACID可能有更多的值,目前先测试两个
还有
--row方案select *
from (
select row_number()over(order by bh desc)rn,*
from (
select top 10000000 0 tc,* from tbrunrecord runrecord0_
where
runrecord0_.MACID ='234'
)t
)tt
where rn>10000000-10
耗时2分27秒
如何缩短到30秒内?
order by bh desc
那就得在 bh 建索引!
已经在bh上面建立聚集索引,bh是主键
from (
select row_number()over(order by bh desc)rn,*
from (
select top 10000000 0 tc,* from tbrunrecord runrecord0_
)t
)tt
where rn>10000000-10去掉where条件后,耗时30秒
select *
from (
select row_number()over(order by bh desc)rn,*
from tbrunrecord runrecord0_
where runrecord0_.MACID ='234'
)tt
where rn>10000000-10
而且显示的行数不符合要求,我只要10条就够了select *
from (
select row_number()over(order by bh desc)rn,*
from tbrunrecord runrecord0_
where runrecord0_.MACID ='234'
)tt
where rn>10000000-10 and rn<=10000000
添加了一个条件后
耗时3分35秒,可以显示10条
select row_number()over(order by bh desc)rn,*
from tbrunrecord runrecord0_
where runrecord0_.MACID ='234' --这个耗时多少,在where条件的字段加索引看看!再者把*换成你需要查找的列名。
正招,我也没招,在我知识范围内的你都做了,同求高人解答,因为我也常这样用row_number分页,只是我的数据量没有达到你的程度。
你重新调整表结构,效率应该也会有所提升吧,但是这么大的数据量,那可是个大工程你自己估计没有决定权的。
我想选取第1000万条开始的10条select row_number()over(order by bh desc)rn,bh
from tbrunrecord runrecord0_
where runrecord0_.MACID ='234' 耗时1分40秒,索引刚才说了,都建立了的。bh是编号,是主键,有聚集索引的。
select bh
from (
select row_number()over(order by bh desc)rn,bh
from (
select top 10000000 0 tc,* from tbrunrecord runrecord0_
where
runrecord0_.MACID ='234'
)t
)tt
where rn>10000000-10
然后根据找到的编号
select * from tbrunrecord where bh in (7820438,7820437,7820436,7820435,7820434,7820433,7820432,7820431,7820430,7820429)耗时3秒!!!
把*换成了主键
问题解决啦!!!