项目完成了,总是被一些效率问题困扰,查看一些资料,总结记录了几种sql分页的执行效率:
方法一:
- 1.
- SELECT TOP 页大小 *
- FROM table1
- WHERE id NOT IN
- (
- SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
- )
- ORDER BY id
方法二:
- SELECT TOP 页大小 *
- FROM table1
- WHERE id >
- (
- SELECT ISNULL(MAX(id),0)
- FROM
- (
- SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
- ) A
- )
- ORDER BY id
- SELECT TOP 页大小 *
- FROM
- (
- SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
- ) A
- WHERE RowNumber > 页大小*(页数-1)
总结:方法1利用id范围not in分页,效率次之
方法2利用id最大值分页,效率最高
方法3利用游标分页,效率最差
转载于:https://blog.51cto.com/1890146/586946