Gridview and effective paging

[nyhetsgrupper]

Hi everyone,

In a datagrid the entire data is read but only the page you want is
displayed to the user when you enable paging. Does the Gridview control
works in the same way?
So if you have 100000 records and you only want 10 records displayed on
each page, does it read all 100000 and display 10, or does it only read
what it needs (Read 10 and display 10)? If it reads all, I can't see
how this control can be useful for anything but small datasources?

[David Jessee]

If you are allowing paging, then yes, every record is cursored
through....that 's the only way you can know how many records are available to
create the paging controls *UGH*

We all have our own solutions....he re's mine.......

if I want page 5...10 items per page....I execute 2 SQL statements on
seperate threads. one is a SELECT COUNT(*) and the other would be a SELECT
TOP 50
I execute read on the datareader 40 times and then return the datareader
which will have 10 records left.

Then set the number of pages for the grid and the data for the grid.

I used to so more exotic SQL for the data selection so that exactly 10
records were selected, but I found that the performance increase when doing
that is negligable for many (but not all) cases. That would be something
along the lines of

SELECT TOP 10 * from Customers where CustomerId not in (select top 40 from
customers)

things get a little hairy if you're filtering and sorting because you have
to put the same WHERE and ORDER BY clauses in the main query and the
subquery. BUT ... if you want the best possible performance and you need to
pass as little data as possible between tiers, that's the way to do it.

(I've had the same thought though......the GridView, and a lot of hte "UI
Magic" does make it wasy to build a BULKY application very quickly)

"ny***********@ gmail.com" wrote:

Hi everyone,

In a datagrid the entire data is read but only the page you want is
displayed to the user when you enable paging. Does the Gridview control
works in the same way?
So if you have 100000 records and you only want 10 records displayed on
each page, does it read all 100000 and display 10, or does it only read
what it needs (Read 10 and display 10)? If it reads all, I can't see
how this control can be useful for anything but small datasources?

[nyhetsgrupper]

Thank you for taking the time to answer me David!

David points out that we all have our own solutions... Can anybody else
tell me how you solve this? Whats your solution?

*Kalle*

David Jessee wrote:

If you are allowing paging, then yes, every record is cursored
through....that 's the only way you can know how many records are available to
create the paging controls *UGH*

We all have our own solutions....he re's mine.......

if I want page 5...10 items per page....I execute 2 SQL statements on
seperate threads. one is a SELECT COUNT(*) and the other would be a SELECT
TOP 50
I execute read on the datareader 40 times and then return the datareader
which will have 10 records left.

Then set the number of pages for the grid and the data for the grid.

I used to so more exotic SQL for the data selection so that exactly 10
records were selected, but I found that the performance increase when doing
that is negligable for many (but not all) cases. That would be something
along the lines of

SELECT TOP 10 * from Customers where CustomerId not in (select top 40 from
customers)

things get a little hairy if you're filtering and sorting because you have
to put the same WHERE and ORDER BY clauses in the main query and the
subquery. BUT ... if you want the best possible performance and you need to
pass as little data as possible between tiers, that's the way to do it.

(I've had the same thought though......the GridView, and a lot of hte "UI
Magic" does make it wasy to build a BULKY application very quickly)

"ny***********@ gmail.com" wrote:

Hi everyone,

In a datagrid the entire data is read but only the page you want is
displayed to the user when you enable paging. Does the Gridview control
works in the same way?
So if you have 100000 records and you only want 10 records displayed on
each page, does it read all 100000 and display 10, or does it only read
what it needs (Read 10 and display 10)? If it reads all, I can't see
how this control can be useful for anything but small datasources?

[Kevin Jones]

If you're using SQL2005 you can use the new ROW_NUMBER() function,
something like (using the pubs database)

use pubs;
go
with OrderedRows as
(select ROW_NUMBER() over (order by au_id) as row , au_id, au_lname,
au_fname from authors)
select * from OrderedRows
where row between 1 and 10
Kevin Jones

ny***********@g mail.com wrote:

Thank you for taking the time to answer me David!

David points out that we all have our own solutions... Can anybody else
tell me how you solve this? Whats your solution?

*Kalle*

David Jessee wrote:

>If you are allowing paging, then yes, every record is cursored
through....tha t's the only way you can know how many records are available to
create the paging controls *UGH*

We all have our own solutions....he re's mine.......

if I want page 5...10 items per page....I execute 2 SQL statements on
seperate threads. one is a SELECT COUNT(*) and the other would be a SELECT
TOP 50
I execute read on the datareader 40 times and then return the datareader
which will have 10 records left.

Then set the number of pages for the grid and the data for the grid.

I used to so more exotic SQL for the data selection so that exactly 10
records were selected, but I found that the performance increase when doing
that is negligable for many (but not all) cases. That would be something
along the lines of

SELECT TOP 10 * from Customers where CustomerId not in (select top 40 from
customers)

things get a little hairy if you're filtering and sorting because you have
to put the same WHERE and ORDER BY clauses in the main query and the
subquery. BUT ... if you want the best possible performance and you need to
pass as little data as possible between tiers, that's the way to do it.

(I've had the same thought though......the GridView, and a lot of hte "UI
Magic" does make it wasy to build a BULKY application very quickly)

"ny*********** @gmail.com" wrote:

>>Hi everyone,

In a datagrid the entire data is read but only the page you want is
displayed to the user when you enable paging. Does the Gridview control
works in the same way?
So if you have 100000 records and you only want 10 records displayed on
each page, does it read all 100000 and display 10, or does it only read
what it needs (Read 10 and display 10)? If it reads all, I can't see
how this control can be useful for anything but small datasources?