SELECT TOP pageSize* FROM Thread WHERE Id NOT IN (SELECT TOP pageIndex * pageSize Id FROM Thread ORDER BY IsTop ASC, LatestReplyDateTime DESC) ORDER BY IsTop ASC, LatestReplyDateTime DESC找了一下原因,主要问题是LatestReplyDateTime排序导致的速度奇慢。Thread也就1000多条,分页要3秒多。LatestReplyDateTime、Id有建立索引,因为LatestReplyDateTime有重复,所以Id是主键。不知道有没有什么办法优化一下?
SELECT TOP pageSize* FROM Thread WHERE LatestReplyDateTime < (SELECT MIN(LatestReplyDateTime) FROM (SELECT TOP pageIndex * pageSize LatestReplyDateTime FROM Thread ORDER BY IsTop ASC, LatestReplyDateTime DESC)) ORDER BY IsTop ASC, LatestReplyDateTime DESC
FROM table1
WHERE id >
(
SELECT ISNULL(MAX(id),0)
FROM
(
SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
) A
)
ORDER BY id
FROM stu_info
WHERE id >=
(
SELECT iif( IsNull(max(A.id)), 0, max(A.id) )
FROM
(
SELECT TOP * FROM stu_info ORDER BY id
) A
)
ORDER BY id