Objectdatasource updating input parameters

In the New stored procedure results, the percentage improvement over the old approach is included for reference: column and the index could be scanned when finding correct subset of records in the final query.However, the index would incur additional overhead when dumping the contents of the table to be paged through into the table variable.Comparing the Performance of the Old and New Approach To ascertain the improvements in performance by restricting the table variable to only including the primary key column(s) from the table to be paged through I used SQL Profiler and compared the different queries on the table with different record counts.

objectdatasource updating input parameters-38objectdatasource updating input parameters-57objectdatasource updating input parameters-79

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.

Introduction When displaying large amounts of data to a user, it's important that the information be presented in digestible chunks rather than slamming it all down their throats at once.

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.

This requires a complete table scan on the data to be paged through.

Instead, we can create the table variable to hold only the primary key column(s) of the table to be paged through.

Additionally, I did not simulate any load that multiple, concurrent visitors to a website would incur.

The results follow and are also avaiable at the end of this article as an Excel spreadsheet.

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).

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.

Tags: , ,