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
实现的都是分页
语句一执行时间小于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
所以很慢
set @max = (select max(CompanyId) from (select top 20 b.CompanyId from SG_AddOns_Company b order by b.CompanyId asc) as tblTmp) select top 20 b.CompanyId AS Id,b.Name from SG_AddOns_Company b where b.CompanyId>
@max
order by b.CompanyId ascselect top 20 b.CompanyId AS Id,b.Name from SG_AddOns_Company b where b.CompanyId>
(select max(CompanyId) from (select top 20 b.CompanyId from SG_AddOns_Company b order by b.CompanyId asc) as tblTmp)
order by b.CompanyId asc
将子查询里的表别名改一改试试,改为与外面查询的别名完全不同.可能子查询里的表别名与外面查询的表别名相同, sql server搞不清它是否是独立的子查询.
set @max = (select max(CompanyId) from (select top 20 b.CompanyId from SG_AddOns_Company b order by b.CompanyId asc) as tblTmp) select top 20 b.CompanyId AS Id,b.Name from SG_AddOns_Company b where b.CompanyId>
@max
order by b.CompanyId asc select top 20 b.CompanyId AS Id,b.Name from SG_AddOns_Company b where b.CompanyId>
(select max(CompanyId) from (select top 20 b.CompanyId from SG_AddOns_Company b order by b.CompanyId asc) as tblTmp)
order by b.CompanyId asc
将你的子查询放于from 后面,应该可提高速度.
将其改为:
select top 20 b.CompanyId AS Id,b.Name
from SG_AddOns_Company b ,
(select max(CompanyId) maxcompanyid
from (select top 20 b.CompanyId
from SG_AddOns_Company b order by b.CompanyId asc) as tblTmp) c where b.CompanyId>c.maxcompanyid
order by b.CompanyId asc
---------------------------
那这就是一个比较有趣的情况了,估计SQL在使用一个表时要聪明一点,能够重用一些查询结果。
具体的情况,你可以仔细看看两组查询的执行计划,就能看出差别。
执行第二个查询时,硬盘灯不停闪动.
按理说,两查询应该是等价的,不知在大内存的情况下,会不会变好.