大家帮个忙有这样一个简单的查询,不知道怎么优化
表有a,b,c
基表a数据量有180万
辅表b,c大概2万左右我的做法是:
select * from
(select * from
(select row=row_number() over(order by ID desc),* from a) resustA
where row between 1 and 30) resustB
inner join b.bid = resustA.bid
inner join c.cid = resustA.cid 速度查询时需要6秒左右
表有a,b,c
基表a数据量有180万
辅表b,c大概2万左右我的做法是:
select * from
(select * from
(select row=row_number() over(order by ID desc),* from a) resustA
where row between 1 and 30) resustB
inner join b.bid = resustA.bid
inner join c.cid = resustA.cid 速度查询时需要6秒左右
(select * from
(select row=row_number() over(order by ID desc),* from a) resustA
where row between 1 and 30) resustB
inner join B ON b.bid = resustA.bid
inner join C ON c.cid = resustA.cid
不知道楼主的是不是应该这样改下
最大的问题就是180万的数据,我row了一次的话,也需要很多时间了
from
(
select row=row_number() over(order by ID desc),*
from a
inner join b
on b.bid = resustA.bid
inner join b
on c.cid = resustA.cid
) resustA
where row between 1 and 30TRY
from
(
select row=row_number() over(order by ID desc),*
from a
inner join b
on b.bid = a.bid
inner join b
on c.cid = a.cid
) resustA
where row between 1 and 30modify
select top 30 * into #a from a order by id
select * from #a
inner join b
onon b.bid = #a.bid
inner join c
onon c.bid = #a.bid
sql套2层查询没有一层查询速度快把
select a.*,b.*,c.* from a,b,c, where a.id=b.id and b.id=c.id and a.id between 1 and 30
大家都给了宝贵的意见谢谢了...
我刚才去回头去执行了一下,基本都不用1秒了,不管是先内联或是先between都不用1秒
可能那会慢 是数据库有问题