Objectdatasource updating input parameters

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.

Modernizing Daniel's Stored Procedure Before we look at improving the performance of Daniel's stored procedure, let's first modernize it a bit.

The stored procedure was written back in 1999 and uses some techniques that aren't "best practices." For one, the stored procedure name is prefixed with Prefix for Procedure Names?

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.

Keep in mind that these results are highly unscientific.

for a more thorough discussion on this topic.) Second, rather than using a temporary table, let's use a table variable.

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.

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

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.

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

Tags: , ,