Home Database Mysql Tutorial sqlserver 三种分页方式性能比较[图文]

sqlserver 三种分页方式性能比较[图文]

Jun 07, 2016 pm 06:02 PM
Performance comparison

第二种分页方法和第一种分页方法效率差不多,但第二种可用于老版本的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,最后一种别用。

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

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

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Revealing the difference between vivox100s and x100: Comprehensive comparison of performance, design and price Revealing the difference between vivox100s and x100: Comprehensive comparison of performance, design and price Mar 19, 2024 am 08:48 AM

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

Function comparison between Oracle11g and Oracle12c Function comparison between Oracle11g and Oracle12c Mar 06, 2024 pm 09:51 PM

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

Performance comparison analysis of golang function types Performance comparison analysis of golang function types Apr 28, 2024 am 10:57 AM

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.

Comparison of discrete graphics cards and integrated graphics cards Comparison of discrete graphics cards and integrated graphics cards Feb 19, 2024 am 10:13 AM

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

Differences between Python and C++ Differences between Python and C++ Sep 03, 2023 pm 02:13 PM

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

Comparative analysis of the implementation methods and performance of C language power function Comparative analysis of the implementation methods and performance of C language power function Feb 25, 2024 pm 04:06 PM

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.

Comparing the performance of Golang and Python: Which one is more efficient? Comparing the performance of Golang and Python: Which one is more efficient? Jan 20, 2024 am 10:12 AM

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? Can Snapdragon 8gen 2 rival Apple in performance? Mar 22, 2024 am 10:18 AM

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

See all articles