Speeding up GridView

[tshad]

I have a GridView in my pages that is very slow since I am sending all the
data back from server and letting the GridView handle the paging.

I need to change that since in one of my projects I could have a couple
thousand rows sent back.

Where does the GridView put the data when it is doing the paging?

Does it call the Sql Server each time it pages it?

I am planning to change the code so that the paging is done on the server.
My Sql would look something like:

With PagedResults AS
(
SELECT EmployeeId, FirstName, MiddleName, LastName, JobTitle, ROW_NUMBER()
OVER (ORDER BY LastName, FirstName) AS ResultSetRowNumber
FROM HumanResources.vEmployee
)
SELECT *
FROM PagedResults
WHERE ResultSetRowNumber > 40 AND ResultSetRowNumber <= 50

Where the Order By and row number would be dynamic.

Would I have to set this up as a dynamic SQL to accomplish this?

Or could I do something like:

With PagedResults AS
(
SELECT EmployeeId, FirstName, MiddleName, LastName, JobTitle, ROW_NUMBER()
OVER (ORDER BY @SortColumns) AS ResultSetRowNumber
FROM HumanResources.vEmployee
)
SELECT *
FROM PagedResults
WHERE ResultSetRowNumber > @StartRow AND ResultSetRowNumber <= @EndRow

Thanks,

Tom

 

[Jonathan Wood]

The datasource control is retrieving the entire results, and then displaying
only those for the current page.

Set your data source to an object, and there is an option to have arguments
for the starting record and number of records. Your method will then need to
retrieve only those rows from the database.

This is critical for any database of substantial size.

 

[Mr. Arnold]

I have a GridView in my pages that is very slow since I am sending all the
data back from server and letting the GridView handle the paging.

I need to change that since in one of my projects I could have a couple
thousand rows sent back.

Where does the GridView put the data when it is doing the paging?

Does it call the Sql Server each time it pages it?

I would control the loading of the Gridview on subsets of data being
returned, like Lastname starts with "A" with a Like statement. Starting
with Lastname starts with "A" would be the first subset seen by the
user. They would be given a TextBox they would use to give "B" or "C" or
"T".

You give the illusion of speed, by not bringing back all the data, only
subsets.

 

[Guest]

I have a GridView in my pages that is very slow since I am sending all the
data back from server and letting the GridView handle the paging.

I need to change that since in one of my projects I could have a couple
thousand rows sent back.

Where does the GridView put the data when it is doing the paging?

Does it call the Sql Server each time it pages it?

I am planning to change the code so that the paging is done on the server..
My Sql would look something like:

With PagedResults AS
(
 SELECT EmployeeId, FirstName, MiddleName, LastName, JobTitle, ROW_NUMBER()
OVER (ORDER BY LastName, FirstName) AS ResultSetRowNumber
 FROM HumanResources.vEmployee
)
SELECT *
FROM PagedResults
WHERE ResultSetRowNumber > 40 AND ResultSetRowNumber <= 50

Where the Order By and row number would be dynamic.

Would I have to set this up as a dynamic SQL to accomplish this?

Or could I do something like:

With PagedResults AS
(
 SELECT EmployeeId, FirstName, MiddleName, LastName, JobTitle, ROW_NUMBER()
OVER (ORDER BY @SortColumns) AS ResultSetRowNumber
 FROM HumanResources.vEmployee
)
SELECT *
FROM PagedResults
WHERE ResultSetRowNumber > @StartRow AND ResultSetRowNumber <= @EndRow

Thanks,

Tom

I think it makes sense to do a custom paging with only records you
need to show on the current page (like you told above). In many cases
user will not go to the next page, so why to return more than he want
to see?