«

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

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.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

"tshad" <> wrote in message
news:...
> 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
>
>

tshad wrote:
> 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.

On Feb 4, 1:33*am, "tshad" <t...@dslextreme.com> wrote:
> 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?