Use custom paging technology to improve database performance

A Web application is a good way to display data in a database. through it, you can make your business complex, database Data with access and security rules can be queried and updated in a simple and intuitive manner. A common criterion for users to judge database applications is the speed of data processing. Many Web pages provide users

A Web application is a good way to display data in a database. through it, you can make your business complex, database Data with access and security rules can be queried and updated in a simple and intuitive manner. A common criterion for users to judge database applications is the speed of data processing. Many Web pages provide users

A Web application is a good way to display data in a database. through it, you can make your business complex, database Data with access and security rules can be queried and updated in a simple and intuitive manner. A common criterion for users to judge database applications is the speed of data processing. Many Web pages provide users with a variety of searchable lists to effectively locate records. A simple and common example is the online book query system, it allows users to retrieve information by author, by title, or by topic.
ASP. NET provides a DataGrid control that allows you to easily create a data list than the previous ASP controls. In addition to the built-in data representations and methods, the DataGrid Control also allows you to define your own representations. The paging technology provides users with convenient data search management. The built-in paging technology of the DataGrid is easy to implement, but when the data size is large, its convenience is at the cost of performance. Next, let's look at how to quickly process the result set of a large amount of data through a custom paging method. The method discussed here is faster and more effective than the default paging method of the DataGrid, because you do not need to send all the data results to the Web server for each request. Instead, it only needs to send the datasets required for each page. For example, if a user only needs to display the result set of 100 pages of 25 records per page on 4th pages, the server only needs to send data in rows, instead of the full data of 1-rows. The default transfer method 1 is shown below:

It can be seen that the built-in paging method of the DataGrid is inefficient. Each request must send the entire query result to the Web server, and the Web server then divides the data into corresponding pages. Although the built-in paging method of the DataGrid is very simple, because of the sequent feature of the Web application, every time a user switches from one page to another, the DataGrid object is destroyed and re-created, which means that the database server must send all the result sets each time.
The custom paging method returns only the result sets to be retrieved, as shown in Figure 2:

As shown in the figure above, the database only needs to return the data records to be displayed each time. First, we create a stored procedure in the database, the Hong Kong virtual host, and there are two input parameters: the first record number of the data to be returned and the last record number, in SQL Server7.0 and later versions, there is a top keyword that limits the number of records returned to the result set before. Unfortunately, there is no way to return data in the middle, for example, 75th to 100 records. Oracle has a rownum () Extension function that can return intermediate records, website space, such as: "select * form Authors where Author_Last_Name = 'anderson 'and rownum ()> = 75 and rownum () <= 100 ". However, since Oracle specifies the rownum value before sorting, such a query "select * from Authors where rownum <= 25 order by Author_Last_Name" will not get the expected results. The method described below is for SQL Server, but the concept here is also applicable to Oracle developers.
To create a stored procedure that returns the results of a specified record, you must first specify the number of records in the returned result set. You can use a temporary table or a table variable (SQL Server 2000 ), there are no major performance differences between the two tables. However, table variables are stored in the memory. If your server has a small amount of memory, you can consider using a temporary table, temporary tables need to be manually released, while table variables are automatically released after the storage process ends.

The following is the stored procedure to be created:

Create proc GetAuthors
@ Author_Last_Name as varchar (100) = null,
@ StartRow as int = null,
@ StopRow as int = null
AS

---- Create a table variable with an identifier Column
Declare @ t_table table
(
[Rownum] [int] IDENTITY (1, 1) Primary key not null,
[Author_Last_Name] [varchar] (40 ),
[Author_First_Name] [varchar] (20 ),
[Phone] [char] (12 ),
[Address] [varchar] (40 ),
[City] [varchar] (20 ),
[State] [char] (2 ),
[Zip] [char] (5)
)

---- Stop processing the query after the specified @ StopRow row is returned
Set RowCount @ StopRow

---- Insert to table variable
Insert @ t_table
(
[Author_Last_Name], [Author_First_Name], [phone], [address], [city], [state], [zip]
)
SELECT [Author_Last_Name], [Author_First_Name], [phone], [address], [city], [state], [zip]

FROM authors
WHERE Author_Last_Name like '%' + @ Author_Last_Name + '%'
Order by Author_Last_Name

---- Return the correct result
SELECT * FROM @ t_table WHERE rownum >=@ StartRow
Order by rownum

GO

The @ StartRow and @ StopRow parameters receive integer values, indicating the start record and end record to be returned. If you want to return page 4th for 25 records, we can set @ StartRow to 76 and @ StopRow to 100. In the table variable @ t_table, we define a column of the integer type named rownum and define it as an identifier column. This column is very important in the paging technology we introduce here, when we insert data, this column is automatically added, which will sort the inserted data. The set rowcount statement is critical to optimizing performance. It tells SQL Server to restrict data to be inserted. If we want data between-records, you do not need to insert data larger than 100 records. The final SQL statement selects the datasets whose rownum is greater than or equal to @ StartRow from the table variable of @ t_table, then returns them to the Web server and binds them to the DataGrid object by the Web server. It is worth noting that if we want to get 76 to 100 records of data, we must insert 100 records of data into the table variable, which means: If the page size requested by the browser is growing, the page performance will also decline. For example, to display data on 100th pages (from 2451st records to 2500th records), we must first fill 2500 records with table variables or temporary tables. Therefore, performance depends on the hardware of your computer and the number of records you want to return. Some tests show that using such a stored procedure in SQL Server 2000 returns 100th pages on average within-50 milliseconds, it takes only 4 milliseconds to return to the first page. Even if 500th pages of data (from 12,451st to 12500 records) are returned, the process can be completed within 650 to 750 milliseconds. It should be said that this situation is rarely seen. However, to reduce the pressure on the database and network transmission, designing a reasonable number of query results pages is very effective.