Objectdatasource updating input parameters

This new keyword simplifies efficiently retrieving a particular subset of data ordered by row number.

After authoring these articles, I received many requests from readers for a look at how to implement this type of efficient custom paging using SQL Server 2000 (which lacks the keyword).

After modernizing and optimizing Daniel's original stored procedure we end up with: CREATE PROCEDURE Paged Results_New ( @start Row Index int, @maximum Rows int ) AS --Create a table variable DECLARE @Temp Items TABLE ( ID int IDENTITY, Employee ID int ) -- Insert the rows from tbl Items into the temp. Greg Hamilton has been kind enough to write up an article exploring various improvements that can speed up my approach by more than an order of magnitude.

table INSERT INTO @Temp Items (Employee ID) SELECT Employee ID FROM Employees -- Now, return the set of paged records SELECT e.*, d.[Name] as Department Name FROM @Temp Items t INNER JOIN Employees e ON e. In short, don't use the stored procedure discussed in this article, but rather check out Greg's advice in A More Efficient Method for Paging Through Large Result Sets.

At first glance it may seem like such an approach would be no better than just returning all of the records.

After all, we're still working with all of the records in table's data in its entirety, the stored procedure only returns 10 records (or however many records per page are configured).

This article looks at updating Daniel's stored procedure to provide an even-more efficient approach.

Moreover, Microsoft recommends: "use table variables whenever possible except when there is a significant volume of data and there is repeated use of the table." Since this stored procedure just dumps data into the temp table once, reads from it, and then is done with it, using a table variable would adhere to Microsoft's recommendation. NET 2.0 the custom paging logic used by the Object Data Source and Grid View defines the records to access not by page index and records per page, but by the start row index and the maximum number of rows to return.NET 2.0 with SQL Server 2005, SQL Server 2005's technique affords much better performance, taking on average a mere 3 milliseconds on average to return 10 records from a total table of 50,000.Compare that to the approach examined in this article, which consumed 307 milliseconds - two orders of magnitude worse!A couple of alert readers have written in to provide some tips on improving the performance of this approach.Greg Hamilton has been kind enough to write up an article exploring various improvements that can speed up my approach by more than an order of magnitude.

Leave a Reply