?
Column
Patement under large data volume
http://www.microsoft.com/china/community/column/49.mspx
Guo Hongjun
?
For very large data models, the page retrieves, each time you load the entire data source is very wasteful. The usual selection is to retrieve the data of the block area of the page size, rather than retrieving all the data, and then perform the current row.
??? This article demonstrates ASP.NET's DataGrid and SQL Server to achieve a paging under large data. In order to facilitate demonstration, the data table uses the ORDERS table of the Northwind database (830 records).
??? If there is a unique self-increment index in the data table, and this field does not have a break-up page. The block area data that retrieves the page size is very simple. This feature can be implemented by simple SQL statements:
Select * from Orders where ORDERID BETWEEN 10248 and 10253
Among them, start numbers: (CurrentPageIndex - 1) * Pagesize? End number: CurrentPageIndex * PageSize
?
??? If this field breaks, or you need to sort the paging according to other conditions, it is more complicated. First get the number you need to display this page,
Then obtain the required block area data according to this number. Getting block area data based on numbers is simple. However, it is not based on the following method.
At this time, you should add the order by command at this time.
Select * from Orders Where ORDERID IN (10248, 10249, 10250, 10251, 10252, 10253) ORDER BY ORDERID DESC? Get this page to display the number list is more complicated, and there are many options: Scenarius 1: Maintain a table This table records these numbered sorting sequences that need to be displayed. (This table can be a temporary table or a physical table). The following demonstrates the use of a global temporary table. This global temporary table records you need to display. Pay attention to sorting, the Order By here is a sort order that needs to be displayed. ? Create table ## temptable (iid int IDENTITY (1, 1) NOT NULL, mainid int NOT NULL) insert ## temptable (mainid) select OrderID from orders order by OrderID desc select * from ## temptable drop table ## temptable - - When actually execution, deleting all temporary tables must not be performed here. • This temporary table exists, which is very simple to get the block data for the specified paging. Look at the following code:?
create table ## temptable (iid int IDENTITY (1, 1) NOT NULL, mainid int NOT NULL) insert ## temptable (mainid) select OrderID from orders order by OrderID desc declare @PageSize int, @ CurrPage int, @ strSQL varchar ( 2000), @ idstr varchar (1000) select @Pagesize = 30 select @Currpage = 2 select @idstr = '' select @idstr = @IDSTR LTRIM (RTRIM (Strim (Strim)) ',' from ## Temptable WHERE IID BETWEEN ((@ currpage-1) * @ PageSize 1) and @ currpage * @ Pagesize if @idstr <> '' begin select @idstr = left (@ idstr, len (@idstr) -1) end select @ Strsql = 'Select * from Orders Where OrderID in (' @ IDSTR ') Order by Orderid Desc' Exec (@STRSQL) Drop Table ## Temptable Note: When I actually use this program, I have to consider when updating this global temporary Table, usually in planning tasks, update this summary table. Solution 2: Every time you go, you get the latest number of numbered order each time. Since there is no such temporary table, you need some strokes that need to display the number of the page, look at the following code:
