Gridview paging and SELECT with no Where clause

[rbrowning1958]

Hello,

I wonder whether someone can explain to me how data is fetched from a
database server when using ASP.NET 2.0's gridview with paging enabled?
My SQL DataSource has a simple "select * from customers" - no where
clause. Using SQL Server's Profiler I can see this same statement is
executed each time I move between pages. Am I right that the ASP
engine on the server is just returning the records required for that
particular page in the grid?

For example - if the grid wants to display relative records 10 thru 19
(say page 2) it still runs select * causing the server to retrieve the
entire table, but only records 10 thru 19 are returned to the
browser?

If this is correct, then after page 2 is rendered to the browser, if a
second user changes record 20 (part of what would be the first user's
3rd page), when the first user requests the third page (records 20
thru 29), then this first user will see the second user's change to
record number 20?

Thanks in advance,

Ray

 

[bruce barker]

you are correct. the default paging is bind to a dataset, and display record
n thru n + page size. if your table is very large, this is not a good
approach. you want to handle the paging in your code.

-- bruce (sqlwork.com)

 

[Stan]

Hello,

I wonder whether someone can explain to me how data is fetched from a
database server when using ASP.NET 2.0's gridview with paging enabled?
My SQL DataSource has a simple "select * from customers" - no where
clause. Using SQL Server's Profiler I can see this same statement is
executed each time I move between pages. Am I right that the ASP
engine on the server is just returning the records required for that
particular page in the grid?

For example - if the grid wants to display relative records 10 thru 19
(say page 2) it still runs select * causing the server to retrieve the
entire table, but only records 10 thru 19  are returned to the
browser?

If this is correct, then after page 2 is rendered to the browser, if a
second user changes record 20 (part of what would be the first user's
3rd page), when the first user requests the third page (records 20
thru 29), then this first user will see the second user's change to
record number 20?

Thanks in advance,

Ray

The GridView will load all records retrieved by the DataSource
whenever the DataBind() method is executed. Filtering is defined and
implemented by the DataSource (using the Select statement and/or its
own RowFilter property) not the Gridview. Paging relies purley on row
indexing and counting to extract the data for the visible page. Indeed
it has to know how many records are available before it can do this.
Hence the behaviour you describe.

Obviously any suitable work-around designed to improve performance
will depend on the application but it's worth considering what
filtering is possible while still meeting the needs of the user. For
example in a very long list of peoples names it might be viable to add
some extra buttons to filter by the the initial letter(s) of the last
name (instead of or in addtion to using the paging buttons) to switch
between views. That way record filtering can be applied at source
level from the event handlers of the buttons.

HTH

 

[rbrowning1958]

Thanks for this Bruce. Confirmation is always a good thing. You are
saying it is not a good approach I am assuming because the SQL
statement is retrieveing all rows, even though they are not returned
to the client? Understood. It's better than I thought though because
initially I thought all rows would returned the the client.

Thanks

Ray

 

[rbrowning1958]

Hi Stan,

I think I understand this. Can you confirm for me thiough that the
filtering, in this case the simple counting of the records for the
page, is done on the server and not the client, i.e. the entire
dataset is not set to the client?

Thanks

Ray