The web application is a very good way to display data in the database. By it, you can provide the business complex, and the database data of access and security rules provides a simple, intuitive way to provide users with a function of query and update. The user determines that the database application is a very common standard that the processing data is slow. Many web pages provide users with a variety of searchable lists to effectively locate recorded locations, a relatively simple and common example is an online book query system, which allows users to follow the author, according to book name or by topics Retrieve book information. ASP.NET provides a DataGrid control to easily create a list of created data than previous ASPs, which allows users to define performance forms in addition to built-in data representations and methods. Paging technology provides convenience for user-managed data lookups. DataGrid built-in paging technology is easy to implement, but when the amount of data is large, its convenience is at the expense of performance. Below, let's take a look at how to quickly handle a large amount of data on the result set of data by custom paging methods. The method we discussed here is rapid and more efficient than the default paging method of DataGrid because each request does not need to send all data results to the web server. Instead, it only needs to send those data sets required for each page. For example: If a user only requires a result set of 25 records on page 4 per page in 100 pages, the server only needs to send data on the 75-100 row, not the full data of 1-1000 rows. The default transfer is as shown in Figure 1:
As can be seen from the figure, the built-in method of DataGrid is not high, and each request must be sent to the web server, and the web server then divides the data into the corresponding page. Despite DataGrid's built-in method, even if it is very simple, because of the unordered features of the web application, the DataGrid object is destroyed and recreated each time, the DataGrid object is destroyed and recreated. The database server must send all the result sets each time. The custom paging method only returns those result sets to be retrieved, as shown in Figure 2 below:
As can be seen from the above figure, the database only needs to return the data record to be displayed each time. First, we create a stored procedure in the database, and there are two input parameters, which are the first record number of data and the last record number. In the version of SQL Server7.0, there is a TOP key. The word restriction returns the number of records number before the result set, but unfortunately, no method can return a part of the data, such as the 75th recorded data. Oracle has a ROWNUM () extension function to return to the middle record, such as "SELECT * form authors where author_last_name = 'Anderson' and rownum ()> = 75 and rownum () <= 100. However, because Oracle specifies the value of ROWNUM before sorting, such queries "SELECT * from authors where rownum <= 25 Order by author_last_name" will not get the result of our expectations. The method we will say below is for SQL Server, but the concept here is also suitable for Oracle developers. To create a stored procedure returned to the specified strip record, you must first specify the number of records that return the result set, you can use the temporary table, you can also use the Table variable (SQL Server 2000), two in performance, there is not much difference, However, the Table variable is stored in memory. If your server is not included, you can consider using a temporary table, a temporary table using a hard disk storage result, and the temporary table needs to manually release the object, and the Table variable is automatically released after the stored procedure. . Below is the stored procedure we have to create:
Create Proc getAuthors @ Author_last_name as varchar (100) = null, @ Startrow as int = null, @ stoprow as int = NULLAS
---- Building a Table Variable Declare @t_Table Table ([Rownum] [INT] Identity (1, 1) PRIMARY NOTNTINTITY (1, 1), [VARCHAR], [VARCHAR], [VARCHAR] (20), [Phone] [25), [Address] [varchar] (40), [City] [varcha] (20), [State] [char] (2), [ZIP] [char] (5))
---- Stop processing query set rowcount @stoprow after returning the specified @stoprow row
---- Insert into the Table variable insert @T_TABLE ([Author_First_name], [Author_First_name], [Phone], [Address], "], [State], [ZIP]) SELECT [Author_Last_name], [Author_First_name], [Phone], [Address], [City], [State], [ZIP]
From authorswhere author_last_name like '%' @Author_last_name '%' Order by Author_Last_Name
---- Return to the correct result Select * from @T_Table where rownum> = @startroworder by Rownumgo
Parameters @startrow and @stoprow Receive integer values, representing the start recording and end record to return, if you want to return to page 4 in a 25 recorded page, we can set @startrow to 76, @ stoprow is 100. We define a column called ROWNUM in Table variable @t_table, and specify as the identifier column, which is important in the paging technology we introduced here. When we insert data, this column is automatically increased. It will be sorted when the data is inserted. Set rowcount statement is critical to optimization performance, telling SQL Server to limit data to be inserted, if we want 76-100 records between records, then you may not need to insert larger than 100 records. The last SQL statement selects ROWNUM greater than or equal to @startrow from @T_table, then returns them to the web server, binds them to the DataGrid object by the web server. It is worth noting that if you want to get 76 to 100 recorded data, we must insert 100 recorded data into the Table variable, which means: If the number of pages requested by the viewer is getting bigger and bigger, the page performance will also have Dropped. For example: To display data on page 100 (recorded from paragraph 2451 to section 2500), we must first populate 2500 records to Table variables or temporary tables, so performance relies on your computer's hardware and you want to return. The number of records, the test indicates that the use of such a stored procedure in SQL Server 2000 returns to page 100 within 200-250 milliseconds, while returning to the first page only needs to be 4 milliseconds. Even if it returns data on page 56 (recorded from 12451 to 12500), it can be completed within 650 to 750 milliseconds. It should be said that this situation is very few. However, in order to reduce the pressure of the database and network transmission, the number of designed query results pages is very effective.
Now, we write a stored procedure to do this work, rather than using the web server to do this, we will do this is to write code for the DataGrid object to use our page tips. DataGrid's sopaging, allowcustompaging, and pagestyle properties help to avoid we write your own code to track records that the visitor is currently in which page access and all requests. We should set allowcustompaging for True, otherwise, you will have trouble when you use DataReader or SqldataReader binding to the DataGrid. In any possible case, try to use SqlDataReader without using DataSet to load the DataGrid object. According to performance tests: When building a list shows data, using SqlDataReader is more than twice as fast as using DataSet. Don't set the values of allowpaging and pagestyle, because if you use these two properties, you must maintain DataGrid in ViewState, but in order to pursue performance optimization, we must set the DataGrid's enableViewState property to false, even though we do You must write a little code to implement our paging, but performance will increase because you don't have to store content in ViewState every time you deal with the web server.
Once we turn off the capabilities saved in ViewState, we must write your code to implement the user from a page to another page. DataGrid If you don't save it in ViewState, then it is no longer tracking record "Previous Page" and "Next". We add a navigation button to help the viewers to navigate. The easiest way is to add two buttons on the page: "Previous" and "Next Page". To go to the next page, we add Click events on the "Next" button to request the corresponding records through our custom paging stored procedure. For example, if the first page consists of Articles 1 to 25 records, then to navigate to the second page, we pass the @startrow of the stored procedure 26, pass the parameters 50 to @stoprow, to return to the first One page, @ Startrow and @stoprow are 1 and 25, respectively. Below is an example of the "Next" event written using VB.NET:
Private sub buttonnext_click (Byval E AS Object, _byval e as system.eventargs) Handles ButtonNext.click
ViewState ("startrow") = viewState ("startrow") DGrid.pageSizeViewState ("stoprow") = viewState ("startrow") DGRID.PAGESIZE
'Run the stored procedure, return SqlDataReaderdgrid.DataSource = runsprocreturndr (Textau_lname.text, _textau_fname.text, viewstate ("stotrow"), ViewState ("stoprow") DGRID.DATABIND ()
End Sub
As can be seen from the above example, we are only @startrow and @stoprow information in ViewState, which is more efficient than saving the entire DataGrid object in ViewState. The "Next" and "Previous" button is only simple navigation, to achieve more detailed navigation information, such as: Total page, custom page record number, etc., but you have to remember not to use DataGrid built-in PagingStyle properties. Depending on the test, the DataGrid does not save DataGrid will increase performance to 54%. List Display Information The performance of the information is important to the viewer's access, and the design of the design is displayed greatly reduces the performance of the application, regardless of how fast it's backend database is. Using custom paging technology, we can avoid the defects brought by the DataGrid default paging mechanism. If you want to implement searchable lists, let your users feel fast and scalable, but also write more code, believe Everyone will write more excellent procedures.
