sql性能优化
有两个表,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

[解决办法]

SQL code

;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