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