sql性能优化

[zhengbin215]

有两个表,TableA 主键id ,TableB 主键 TaskId,根据taskid查询所有记录, TableA内taskId有很多重复,大概一个taskid可以对应成千上万条记录, tableA大概有2000W条记录, 现在根据分页如下查询语句, tableA 在taskId上有聚焦索引, 当页码在1万以内时,很快可以达到,但是到5w页 或以上时,页面等待时间越长,有时还超时, 请问有什么办法可以优化下.
;WITH tab AS
(SELECT A.ID,A.TaskID,A.Uid,A.Email,A.Phone,A.Type,A.CreateDate,
ROW_NUMBER() OVER (ORDER BY A.InquiryID) AS RowNumber
FROM TableA AS A WITH(NOLOCK)
INNER JOIN TableB AS B WITH(NOLOCK) ON (A.TaskID = B.TaskID)
)
SELECT * FROM tab WHERE
TaskID=TaskID AND
RowNumber BETWEEN @num AND @num+20

[--小F--]

分页都分到 5W页了 等高手。

[勿勿]

没见过这么多页的

[jmx123456789]

学习下 很少有机会碰到这么大数据量

[kingtiy]

;WITH tab AS
  (SELECT A.ID,A.TaskID,A.Uid,A.Email,A.Phone,A.Type,A.CreateDate,
   ROW_NUMBER() OVER (ORDER BY A.TaskID) AS RowNumber--排序放在聚集索引上.应该会更快
--ROW_NUMBER() OVER (ORDER BY A.InquiryID) AS RowNumber
  FROM TableA AS A WITH(NOLOCK)
  INNER JOIN TableB AS B WITH(NOLOCK) ON (A.TaskID = B.TaskID)  
  )  
  SELECT * FROM tab WHERE  
  TaskID=TaskID AND
  RowNumber BETWEEN @num AND @num+20

[zhengbin215]

楼上很给力,谢谢. 50w内没有问题了.已经是页末了