Sqlsever Big Data Paging "turn"

2014-09-26 08:53:42

"Article Source: http://www.cnblogs.com/wlandwl/archive/2014/09/25/bigpage.html"

The paging of big data in SQL Sever has always been a difficult piece to handle, and there are shortcomings in the use of ID self-adding columns. From a relatively comprehensive page view, the new Row_number () function in SQL sever2005 solves this problem. Let's start by introducing a practical project. China Railway Construction Company's project table data is very large, the beginning of development with the GridView control comes with the paging, after running a year, click on the next page is finally difficult to wait, the system needs to be optimized. The improvement of paging is a business need. So, I used the row_number () function paging. is also a solution to the urgent.

Note: This article is only a case description, more detailed theory please see the essay "Big Data Paging implementation and performance optimization"

The main factors to improve the paging efficiency are:
(1) Make full use of foreign keys to improve table connection speed
(2) Use natural links as much as possible to avoid external connections
(3) Minimize the number of records on the left side of the connection to reduce the amount of records processed first
(4) Select as much as possible, then connect to reduce the amount of data processed
(5) When possible, build a view that fully uses the database's own optimization capabilities

The following is the SQL statement for the paging query:

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 are null ) or (SGDW = ' gf12 ')) or lry = ' Gf12gao '))
As B
where idrank>=50023 and idrank<50033

The value of the Idrank can be passed through the Web page. The paging SQL statement is written in the stored procedure, can fully show the effect of paging.

Figure A single table paging time-consuming

Figure two Multi-table paging time-consuming

Sqlsever Big Data Paging "turn"