tailieunhanh - Improving Paging Performance

[ Team LiB ] Recipe 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 | Team LiB Recipe 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 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 Orderld An input parameter that contains the OrderlD of the first record of the client s current page of Orders data. @Last Orderld An input parameter that contains the OrderlD of the last record of the client s 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_PageOrders ALTER PROCEDURE SP0904_PageOrders @PageCommand nvarchar 10 @FirstOrderId int null @LastOrderId int null @PageCount int output @CurrentPage int output AS 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 1 begin select top 10 from Orders order by OrderID set @CurrentPage 1 return 0 end -- last page is requested or next page when the current -- page is already the last if @PageCommand LAST or @PageCommand NEXT and @CurrentPage @PageCount begin select a. from select TOP 10 from orders order by OrderID desc a order by OrderID set @CurrentPage .