[Share] Summary of several types of data collected from the Internet by PAGE

 

I collected two articles from the Internet on the analysis and summary of database paging performance. The paging method is similar.

 

Article 1: blog reposted from comaple

 

This experiment is to explore the performance of paging. Of course, client paging is also a paging policy. However, this paging method is outdated and is not recommended. Here we will only discuss the server side paging.

 

Lab environment:

Pentium (R) dual-Core CPU E5300 @ 2.6 GHz 2.59 GHz, GB memory

 

SqlServer2008 database environment, the tables we need in the database:

Dbo. GMpipe

Create table [dbo]. [GMpipe] (

[GMDataID] [uniqueidentifier] not null,

[PointID] [uniqueidentifier] NULL,

[MeasurePipe] [varchar] (10) NULL,

[MeasureTime] [datetime] NULL,

[MeasureCycle] [varchar] (10) NULL,

[MeasureData] [int] NULL,

[DoseRateValue] [decimal] (18, 10) NULL,

CONSTRAINT [PK_GMPIPE] PRIMARY KEY CLUSTERED

(

[GMDataID] ASC

) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Currently, this table contains 1157226 data records, and the query time using the select statement is 17 s.

SELECT * FROM dbo. GMpipe order by measureTime DESC

 

Next, let's try it out:

Method 1

Use the top Statement (this document only lists commonly used statements ):

The paging stored procedure has been implemented as follows:

Create procedure paging1

@ PageNum INT -- page number

, @ Num INT -- number of entries per page

AS

BEGIN

Select top (@ Num) * FROM

(

Select top (@ Num * @ pageNum) * FROM dbo. GMpipe order by dbo. GMpipe. measureTime asc

) B order by B. measureTime DESC;

END

Go

This method first extracts the previous @ Num * @ pageNum data in the database, and then extracts the last @ Num data from the result set. Of course, the two sorting rules are different, otherwise, the paging effect is unavailable. You can give it a try.

Performance
EXEC paging1; -- five entries per page, the tenth page data time: 1 s

EXEC paging1 200th, 5; -- five pages per page, page data time: 1 s

EXEC paging1 20,000th, 5; -- five pages per page, page data time: 1 s

EXEC paging1 20th, 5; -- five pages per page, million pages data time: 3 s

 

Method 2

Use temporary tables

The paging stored procedure is implemented as follows:

Create procedure paging2

@ PageNum INT

, @ Num INT

AS

BEGIN

SELECT measurePipe, measureTime, measureCycle, MeasureData, doseRateValue, IDENTITY (int) Num INTO # temp FROM dbo. GMpipe order by measureTime ASC

SELECT * FROM # temp WHERE Num <= @ Num * @ pageNum AND Num> @ Num * (@ pageNum-1)

Order by Num ASC

Drop table # temp

END

Go

This method is to locate all the data in the table, add the column Num that identifies the row number, and load it into the temporary table # temp, and then perform paging query based on the row number column.

Performance
EXEC paging2 2, 5; -- five entries per page, the second page data time: 3 s

EXEC paging2 200th, 5; -- five pages per page, page data time: 3 s

EXEC paging2 20 thousand, 5; -- five entries per page, page data time: 3 s

EXEC paging2 20th, 5; -- five pages per page, million pages data time: 3 s

Method 3

Use the ROW_NUMBER () function provided by the system

The storage process is implemented as follows:

Create procedure paging0

@ PageNum INT

, @ Num INT

AS

Begin

SELECT * FROM

(

SELECT measurePipe, measureTime, measureCycle, MeasureData, doseRateValue, ROW_NUMBER () OVER (order by GMpipe. measureTime ASC) AS NUM

FROM GMpipe)

Where a. NUM <= @ Num * @ pageNum and a. NUM> @ Num * (@ pageNum-1) order by a. measureTime desc

END

Go

This method is not much said. You can understand it at a glance and directly look at the performance.

Performance
EXEC paging0 20th; -- five entries per page, page data time: 1 s

EXEC paging0 20 thousand, 5; -- five entries per page, page data time: 1 s

EXEC paging0 20th, 5; -- five pages per page, million pages data time: 1 s

Method 3:

  

The third way to improve is because the Top keyword is actually

The reason why the performance has been optimized is better than the execution efficiency of ROW_NUMBER () is because it has been used twice. So, why don't we use the two in combination. Let's improve it.

 

Create procedure paging0

@ PageNum INT

, @ Num INT

AS

Begin

SELECT * FROM

(

Select top (@ Num * @ pageNum) measurePipe, measureTime, measureCycle, MeasureData,

DoseRateValue, ROW_NUMBER () OVER (order by GMpipe. measureTime ASC) AS NUM

FROM GMpipe)

Where a. NUM> @ Num * (@ pageNum-1) order by a. measureTime desc

END

Go

In this way, the execution efficiency is higher!

Summary

Let's change the number of entries per page.

Temporary table method:

EXEC paging2 5000,200; -- two hundred entries per page, 5,000th page data time: 7 s

Top statement method:

EXEC paging1 5000,200; -- two hundred entries per page, 5,000th page data time: 3 s

ROW_NUMBER () function method:

EXEC paging0 5000,200; -- five pages per page, 20th million pages data time: 1 s

Analysis: we can see clearly that the factors that affect the top statement method are the number of pages you want to obtain, that is, the more time it takes to lean back. The factors that affect the temporary table are more, first, the total number of data items, and second, the paging mode, that is, the data volume on each page. The ROW_NUMBER () function may only affect the total data size, and the performance is good!

I think the page size of data on the 0.2 million page is enough for general systems! We can't see more.

 

 Article 2. Source of reprintLi honggen's blog

 

 

The paging of the SQL Server Stored Procedure has been discussed for several years. Many friends are asking me, so I would like to express my point of view here.
Create a table:

Create table [TestTable] (
[ID] [int] IDENTITY (1, 1) not null,
[FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL,
[LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL,
[Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

 

Insert data: (20 thousand, more data will be used for testing)
SET IDENTITY_INSERT TestTable ON

Declare @ I int
Set @ I = 1
While I <= 20000
Begin
Insert into TestTable ([id], FirstName, LastName, Country, Note) values (@ I, 'firstname _ XXX', 'lastname _ XXX', 'country _ XXX ', 'note _ XXX ')
Set @ I = @ I + 1
End

SET IDENTITY_INSERT TestTable OFF

 

-------------------------------------

Paging solution 1: (use Not In and select top pages)
Statement format:
Select top 10 *
FROM TestTable
WHERE (ID NOT IN
(Select top 20 id
FROM TestTable
Order by id ))
ORDER BY ID

Select top page size *
FROM TestTable
WHERE (ID NOT IN
(Select top page size * Page id
FROM table
Order by id ))
ORDER BY ID

-------------------------------------

Paging solution 2: (use the ID greater than the number and select top pages)
Statement format:
Select top 10 *
FROM TestTable
WHERE (ID>
(Select max (id)
FROM (select top 20 id
FROM TestTable
Order by id) as t ))
ORDER BY ID

Select top page size *
FROM TestTable
WHERE (ID>
(Select max (id)
FROM (select top page size * Page id
FROM table
Order by id) as t ))
ORDER BY ID

-------------------------------------

Paging solution 3: (using SQL cursor Stored Procedure paging)
Create procedure XiaoZhengGe
@ Sqlstr nvarchar (4000), -- query string
@ Currentpage int, -- page N
@ Pagesize int -- number of lines per page
As
Set nocount on
Declare @ P1 int, -- P1 is the cursor id
@ Rowcount int
Exec sp_cursoropen @ P1 output, @ sqlstr, @ scrolopt = 1, @ ccopt = 1, @ rowcount = @ rowcount output
Select ceiling (1.0 * @ rowcount/@ pagesize) as total number of pages --, @ rowcount as total number of rows, @ currentpage as current page
Set @ currentpage = (@ currentpage-1) * @ pagesize + 1
Exec sp_cursorfetch @ P1, 16, @ currentpage, @ pagesize
Exec sp_cursorclose @ P1
Set nocount off

Other solutions: If there is no primary key, you can use a temporary table or solution 3, but the efficiency is low.
We recommend that you add primary keys and indexes during optimization to improve query efficiency.

The SQL query Analyzer displays a comparison: My conclusion is:
Paging solution 2: (using more than ID and select top pages) is the most efficient. You need to splice an SQL statement
Paging solution 1: (using Not In and select top pages) The efficiency is second, and SQL statements need to be spliced.
Paging solution 3: (using SQL cursor Stored Procedure paging) The efficiency is the worst, but the most common

In actual situations, specific analysis is required.

**************************************** ****************************

I just reprinted the article and did not sort it out. Hope to inspire and help the development!