sqlserver 三种分页方式性能比较[图文]
第二种分页方法和第一种分页方法效率差不多,但第二种可用于老版本的sqlserver甚至access,最后一种别用
Liwu_Items表,CreateTime列建立聚集索引第一种,sqlserver2005特有的分页语法
代码如下:
declare @page int
declare @pagesize int
set @page = 2
set @pagesize = 12
SET STATISTICS IO on
SELECT a.* FROM (
SELECT ROW_NUMBER() OVER (ORDER BY b.CreateTime DESC) AS [ROW_NUMBER], b.*
FROM [dbo].[Liwu_Items] AS b ) AS a
WHERE a.[ROW_NUMBER] BETWEEN @pagesize + 1 AND (@page*@pagesize)
ORDER BY a.[ROW_NUMBER]
结果:
(12 行受影响)表 'Liwu_Items'。扫描计数 1,逻辑读取 7 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
逻辑读是7次
执行计划:
主要开销都在聚集索引扫描了。
第二种,用两个top分别正序和倒序排列,共另个子查询来实现分页,
代码如下:
declare @page int
declare @pagesize int
set @page = 2
set @pagesize = 12
SET STATISTICS IO on
select * from (
select top (@pagesize) * from
(select top (@page*@pagesize) * from Liwu_Items order by CreateTime desc) a
order by CreateTime asc) b
order by CreateTime desc
结果
(12 行受影响)表 'Liwu_Items'。扫描计数 1,逻辑读取 7 次,物理读取 0 次,预读 317 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
执行计划
执行计划和第一种差不多,但两个排序占的资源挺多的。
第三种,最垃圾的一种,用not in字句实现的,如下
代码如下:
declare @page int
declare @pagesize int
set @page = 2
set @pagesize = 12
SET STATISTICS IO on
select top(@pagesize) * from Liwu_Items
where ItemId not in(
select top((@page-1)*@pagesize) ItemId from Liwu_Items order by CreateTime desc)
order by CreateTime Desc
结果
(12 行受影响)表 'Worktable'。扫描计数 1,逻辑读取 70 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。表 'Liwu_Items'。扫描计数 2,逻辑读取 18 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
性能最差,对两个表进行处理,逻辑读都很高,汗。
执行计划
这执行计划都看不懂,嵌套循环和表假脱机占了很大的资源。
总结:第二种分页方法和第一种分页方法效率差不多,但第二种可用于老版本的sqlserver甚至access,最后一种别用。

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Revealing the differences between vivox100s and x100: Comprehensive comparison of performance, design, and price. As the smartphone market continues to develop, competition among mobile phone brands has become increasingly fierce. vivox100s and x100 are two new products that have attracted much attention and are highly anticipated by consumers. What are the similarities and differences between these two mobile phones in terms of performance, design, price, etc.? This article will give you a comprehensive comparison. First, let's look at the comparison in terms of performance. vivox100s is equipped with the latest Snapdragon 865 processor, which has strong performance and can fully

Oracle Database is one of the most popular relational database management systems in the world. In recent years, Oracle has successively launched two versions, Oracle11g and Oracle12c. They have many features in common, but also have some significant differences. This article will conduct a comparative analysis of the functions of the two versions and provide some specific code examples to help readers better understand the differences between them. 1. Functional features of Oracle11g: partition table and partition index: Oracle1

In Go language, function types have a significant impact on performance. Performance comparison shows that ordinary functions are the best (147.08MOPS), followed by anonymous functions (158.01MOPS), and finally closures (10.02MOPS). These types have different advantages in different scenarios: anonymous functions are suitable for callbacks, closures are suitable for state management, and ordinary functions are suitable for performance optimization.

Which is better, independent graphics card or integrated graphics card? With the development of computer technology, graphics card, as an important hardware device, plays a vital role in computers. For ordinary users, they are often faced with a choice - whether a discrete graphics card or an integrated graphics card is more suitable for their needs. This article will discuss the advantages and disadvantages of independent graphics cards and integrated graphics cards in terms of performance, power consumption, price, applicable scenarios, etc., to help readers better understand the differences between the two. First, let's look at the performance aspect. A discrete graphics card is a graphics card that is independent of the motherboard and has its own video memory

C++ is a mid-level, case-sensitive, object-oriented language. Bjarne Stroustrup created C++ at Bell Labs. C++ is a platform-independent programming language that runs on Windows, MacOS, and UNIX. C++ is closer to the hardware and allows low-level programming. This gives you control over memory, improved performance, and reliable software. Python is a general-purpose high-level programming language. Python is used for web development, machine learning, and other cutting-edge software. Python is suitable for new and experienced C++ and Java programmers. Guido Van Rossam created Python in 1989 at the Netherlands National Institute. Python in 1991

Implementation method and performance comparison analysis of C language power function Introduction: Power operation is a very common and important operation in mathematics and computer science. It is used to calculate the nth power of a number. As a programming language widely used in system-level development, C language provides a variety of ways to implement exponentiation functions. This article will analyze three common methods: brute force method, iterative method and recursive method, and compare their efficiency and applicability through performance testing. Method 1: Brutal method The brute force method is the simplest and most direct method, which is to perform n consecutive multiplication operations.

Performance comparison between Golang and Python: Who is better? With the rapid development of the software development industry, it has become particularly important to choose a programming language that is both efficient and applicable. This article will discuss the performance comparison of two popular programming languages, Golang and Python, aiming to help readers understand the advantages of the two in different aspects so as to make the right choice for their needs. In order to demonstrate the comparison results more concretely, this article will provide several code examples for comparison. First, let’s take a look at Golang and Python

Can Snapdragon 8gen 2 rival Apple in performance? In recent years, competition in the mobile phone market has become increasingly fierce, with major brands launching flagship mobile phones one after another in the hope of attracting more consumers. As the two giants in the field of mobile phone chips, Qualcomm's Snapdragon series and Apple's A series have always attracted much attention. Among them, Snapdragon 8gen2, as the latest flagship chip launched by Qualcomm, has attracted much attention in terms of performance. Can it be comparable to Apple's chips in terms of performance? Let’s explore it together. First, let’s take a look at Snapdragon 8gen2
