Improving Paging Performance

[ Team LiB ] Recipe 9.4 Improving Paging Performance Problem Given an application that allows the user to page through a large result set in a data grid, you need to improve the performance of the paging. Solution Build a custom paging solution that overcomes the performance limitations of the overloaded Fill
Nội dung trích xuất từ tài liệu:
Improving Paging Performance [ Team LiB ]Recipe 9.4 Improving Paging PerformanceProblemGiven an application that allows the user to page through a large result set in a data grid,you need to improve the performance of the paging.SolutionBuild a custom paging solution that overcomes the performance limitations of theoverloaded Fill( ) method of the DataAdapter.The sample uses a single stored procedure, which is shown in Example 9-5:SP0904_PageOrders Used to return 10 records from the Orders table of the Northwind database that correspond the first, last, next, or previous page, or a specific page. The procedure has the following arguments: @PageCommand An input parameter that accepts one of the following values: FIRST, LAST, PREVIOUS, NEXT, or GOTO. This specifies the page of results to return to the client. @First OrderId An input parameter that contains the OrderID of the first record of the clients current page of Orders data. @Last OrderId An input parameter that contains the OrderID of the last record of the clients current page of Orders data. @PageCount An output parameter that returns the number of pages, each of which contains 10 records, in the result set. @CurrentPage An output parameter that returns the page number of the result set returned.Example 9-5. Stored procedure: SP0904_PageOrdersALTER PROCEDURE SP0904_PageOrders @PageCommand nvarchar(10), @FirstOrderId int = null, @LastOrderId int = null, @PageCount int output, @CurrentPage int outputAS SET NOCOUNT ON select @PageCount = CEILING(COUNT(*)/10) from Orders -- first page is requested or previous page when the current -- page is already the first if @PageCommand = FIRST or (@PageCommand = PREVIOUS and @CurrentPage = @PageCount) begin select a.* from (select TOP 10 * from orders order by OrderID desc) a order by OrderID set @CurrentPage = @PageCount return 0endif @PageCommand = NEXTbegin select TOP 10 * from Orders where OrderID > @LastOrderId order by OrderID set @CurrentPage = @CurrentPage+1 return 0endif @PageCommand = PREVIOUSbegin select a.* from ( select TOP 10 * from Orders where OrderId < @FirstOrderId order by OrderID desc) a order by OrderID set @CurrentPage = @CurrentPage-1 return 0endif @PageCommand = GOTObegin if @CurrentPage < 1 set @CurrentPage = 1 else if @CurrentPage > @PageCount set @CurrentPage = @PageCount declare @RowCount int set @RowCount = (@CurrentPage * 10) exec (select * from (select top 10 a.* from (select top + @RowCount + * from Orders order by OrderID) a order by OrderID desc) b order by OrderID) return 0 end return 1The sample code contains six event handlers and a single method:Form.Load Sets up the sample by loading the schema for the Orders table from the Northwind database into a DataTable. Next, a DataAdapter is created to select records using the stored procedure to perform the paging through the DataTable. The GetData( ) method is called to load the first page of Orders data.GetData( ) This method accepts a page navigation argument. The parameters for the stored procedure created in the Form.Load event are set. The Fill( ) method of the DataAdapter is called to execute the stored procedure to retrieve the specified page of records and the output parameters of the stored procedure—@PageCount and @CurrentPage—are retrieved.Previous Button.Click Calls the GetData( ) method with the argument PREVIOUS to retrieve the previous page of data from the Orders table.Next Button.Click Calls the GetData( ) method with the argument NEXT to retrieve the next page of data from the Orders table.First Button.Click Calls the GetData( ) method with the argument FIRST to retrieve the first page of data from the Orders table.Last Button.Click Calls the GetData( ) method with the argument LAST to retrieve the last page of data from the Orders table.Goto Button.Click Sets the value of the current page to the specified page value and calls the GetData( ) method with the argument GOTO to retrieve that page of data from the Orders table.The C# code is shown in Example 9-6.Example 9-6. File: ImprovePagingPerformanceForm.cs// Namespaces, variables, and constantsusing System;using System.Configuration;using System.Windows.Forms;using System.Data;using System.Data.SqlClient;private SqlDataAdapter da;private DataTable table;// Stored procedure name constantspublic const String PAGING_SP = SP0904_PageOrders;// Field name constantsprivate const String ORDERID_FIELD = OrderID;private int currentPage;private int firstOrderId;private int lastOrderId;// . . .private void ImprovePagingPerformanceForm_Load(object sender, System.EventArgs e){ // Get the schema for the Orders table. da = new SqlDataAdapter(SELECT * FROM Orders, ConfigurationSettings.AppSettings[Sql_ConnectString]); table = new DataTable(Orders); da.FillSchema(table, SchemaType.Source); // Set up the paging stored procedure. SqlCommand cmd = new SqlCommand( ); cmd.CommandText = PAGING_SP; cmd.Connection = new SqlConnection( ConfigurationSettings.AppSettings[Sql_ConnectString]); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(@PageCommand, SqlDbType.NVarChar, 10); cmd.Parameters.Add(@FirstOrderId, SqlDbType.Int); cmd.Parameters.Add(@LastOrderId, SqlDbType.Int); cmd.Parameters.Add(@PageCount, SqlDbType.Int).Direction = ParameterDirection.Output; cmd.Parameters.Add(@CurrentPage ...