存储过程优化,排序开销大
with result0 as ( select bId,bcId,ja,jb,jc from jtable )
select * from (
select ROW_NUMBER() OVER (order by b.bId,r.bbId) rownum,b.bId ,b.bname ,b.sN , r.bcid, r.ja, r.jb, r.jc,
from BSI b
left join Area area on b.areaId=area.areaId
right join result0 as r on r.bId=b.bId
) pagination where pagination.rownum between 1 and 10
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY b.bId, r.bbId ) rownum ,
b.bId ,
b.bname ,
b.sN ,
r.bcid ,
r.ja ,
r.jb ,
r.jc
FROM jtable AS r
LEFT JOIN BSI b ON r.bId = b.bId--#2.如果这儿能用 INNER JOIN 的话,应该会更快些
LEFT JOIN Area area ON b.areaId = area.areaId
) pagination
WHERE pagination.rownum BETWEEN 1 AND 10
--#1.添加3个索引,如果有,就不加了。
--索引1
CREATE INDEX IX_BSI_bId ON dbo.BSI
(
bId
) INCLUDE(bname, sN)
--索引2
CREATE INDEX IX_jtable_bId_bbId ON dbo.jtable
(
bId,
bbId
) INCLUDE(bcid, ja, jb, jc)
--索引3
CREATE INDEX IX_Area_fieldlist ON dbo.Area
(
areaId
)
select top 10
b.bId, b.bname, b.sN, r.bcid, r.ja, r.jb, r.jc
from BSI b
left join Area area on b.areaId=area.areaId
right join jtable as r on r.bId=b.bId
order by b.bId,r.bbId
SELECT top 10 b.bId ,b.bname ,b.sN ,r.bcid ,r.ja ,r.jb ,r.jc
FROM jtable AS r
LEFT JOIN BSI b ON r.bId = b.bId
LEFT JOIN Area area ON b.areaId = area.areaId
order by b.bId, r.bbId
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY b.bId, r.bbId ) rownum ,
b.bId ,
b.bname ,
b.sN ,
r.bcid ,
r.ja ,
r.jb ,
r.jc
FROM jtable AS r
LEFT JOIN BSI b ON r.bId = b.bId
LEFT JOIN Area area ON b.areaId = area.areaId
) pagination
WHERE pagination.rownum BETWEEN 1 AND 10
select top 10
b.bId, b.bname, b.sN, r.bcid, r.ja, r.jb, r.jc
from BSI b
left join Area area on b.areaId=area.areaId
right join jtable as r on r.bId=b.bId
order by b.bId,r.bbId