SqlSever Big Data paging and SqlSever data Paging

SqlSever Big Data paging and SqlSever data Paging

In SQL Server, the paging of big data has always been a hard part to be processed, and the use of id auto-incrementing column paging also has shortcomings. From a relatively comprehensive page view, the row_number () function added in SQL sever2005 solves this problem. Let's start with an actual project. The project table of China Iron Construction Corporation has a large amount of data. At the beginning of its development, the page of the GridView control was used. After one year of operation, it was hard to wait for the next page, and the system had to be optimized. The improvement of paging is a business need. So I used the Row_number () function for paging. It solves the urgent need.

Note: This article is only a case description. For more detailed theories, see the "Big Data paging implementation and performance optimization" article.

Pagination efficiency can be improved mainly:
(1) Make full use of foreign keys to increase the table connection speed
(2) Try to use natural links and avoid external connections
(3) Place tables with fewer records on the left of the join to reduce the number of records processed first.
(4) Select and connect as much as possible to reduce the amount of data processed
(5) create a view whenever possible and make full use of the optimization functions of the database.

The following are SQL statements for querying by page:

Select * from
(Select t_gcxm_test.sgdw, t_gcxm_test.id, xmmc, t_gcxm_test.gclb as gclb, gchte, gchte-kl2 as xmsyjzl,
Kl2 as klsl, dwmc, lry, Row_number () over (order by t_gcxm_test.id desc) as IDRank
From t_dw, t_gcxm_test where t_dw.dwid = t_gcxm_test.sgdw and (sgdw like 'gf12% 'and (sgdw <> 'gf12' and fgcid is null) or (sgdw = 'gf12 ')) or lry = 'gf12gao '))
As B
Where IDRank> = 50023 and IDRank <50033

The IDRank value can be passed through the web page. Write the paging SQL statement in the stored procedure to fully display the paging effect.

Figure 1 paging time of a single table

Figure 2 time-consuming multi-Table Paging

How does one implement paging in SQL Server?

Use the paging function of SQL server 2005, which is very convenient!
Set ANSI_NULLS ON
Set QUOTED_IDENTIFIER ON
Go

Alter procedure [dbo]. [DataList]
@ PageNum int
AS
BEGIN

Set nocount on;

SELECT * FROM (SELECT ROW_NUMBER () OVER (order by [OrderID] DESC) AS RowID, * FROM [OrderList]) AS OrderTable WHERE RowID BETWEEN @ PageNum AND @ PageNum + 20
END
 
SQL Server Data Paging

Rownum is currently highly efficient and has been optimized by Microsoft!