为什么这两条sql 语句的执行效率差别这么大?
Company表中大约500万条数据,CompanyBought中大约5000条数据。
实现的都是分页
语句一执行时间小于1秒,语句二大约需要一分钟
语句一就是把语句二分成两条sql执行而已
为什么差别这么大呢?
请指点。
thanks
语句一
declare @max int
set @max = (select max(CompanyId) from (select top 20 b.CompanyId from CompanyBought a JOIN Company b ON a.CompanyId = b.CompanyId order by b.CompanyId asc) as tblTmp)
select top 20 b.CompanyId AS Id,b.Name from CompanyBought a JOIN Company b
on a.CompanyId = b.CompanyId where b.CompanyId>@max
order by b.CompanyId asc
语句二
select top 20 b.CompanyId AS Id,b.Name from CompanyBought a JOIN Company b
on a.CompanyId = b.CompanyId where b.CompanyId>
(select max(CompanyId) from (select top 20 b.CompanyId from CompanyBought a JOIN Company b ON a.CompanyId = b.CompanyId order by b.CompanyId asc) as tblTmp)
order by b.CompanyId asc