SQL Server 2000 custom page

Currently, page turning is usually performed on the default page of The DataGrid. Although only data on the current page is bound in this way, the dataset returned from the database is still the entire dataset, if the query returns a large data set, it will take a lot of time to flip the page, because it wastes network bandwidth and server resources (bind a page, but still returns the entire dataset ). therefore, the optimization principle for this situation is: if the default page of The DataGrid is not used and the custom page is used, the data on the page to be displayed will be returned. you can use the following two methods to solve the data paging problem:. SQL statement; B. stored Procedure

Test Environment: A table containing two fields: temptable, tempid (primary key, sequence ID), and tempguid (information, one guid). Rows of data are inserted into the table.

 

Use the default page flip mode of the DataGrid:

The average time required to flip a page is about 8000 milliseconds. (The test time used here is the time required from the page PostBack to the time after the render method is executed.

 

Use custom paging:

A. SQL statement method (1)

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

Sample Code-

Private void page_load (Object sender, system. eventargs e) {If (! Page. ispostback) {binddata (0); // execute the data bound to page 1} else {dt1 = datetime. now; // initial time} protected override void render (htmltextwriter writer) {base. render (writer); dt2 = datetime. now; // End Time timespan Ts = dt2-dt1; // response of the time consumed. write ("alert ('" + ts. totalmilliseconds + "')");} private void binddata (INT pageindex) {int recordcount = 0; // defines the total number of records dataset DS = getdata (pageindex, ref recordcount ); // obtain the data dgtest on the current page. virtualitemcount = recordcount; // dgtest. datasource = Ds. tables [0]. defaultview; dgtest. databind ();} private dataset getdata (INT pageindex, ref int recordcount) {string strsql = @ "select top 20 * From temptable
Where tempid not in (select top "+ (20 * pageindex)
+ "Tempid from temptable order by tempid) order by tempid"; string strcount = @ "select count (*) from temptable"; dataset DS = new dataset (); sqlconnection con =
New sqlconnection (
@ "Server = localhost; user id = sa; Password = sa; database = Castle; Connection Reset = false"); sqlcommand command = new sqlcommand (strsql, con); con. open (); sqldataadapter d = new sqldataadapter (command); D. fill (DS); command. commandtext = strcount; sqldatareader sr = command. executereader (); Sr. read (); recordcount = sr. getint32 (0); Sr. close (); con. close (); Return Ds ;}

 

In this way, it takes about 250 milliseconds to flip rows of data.

Disadvantages of this method: 1. SQL needs to be pieced together 2. if the page number is too large, it may be slower. (Because not in, it may not be a problem, because no one will turn to the next page)

 

SQL statement Method 2:

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