rosekangaroo
New Member
I am looking for a good example of custom paging in ASP.Net with a datagrid. The recordset I am retrieving is about 300,000 records. Under normal paging the entire dataset is rebuilt/visited, which makes the page load very slowly. I have read that custom paging with the datagrid doesn't suffer from this problem. I just can't find an example for it. I tried microsofts example and can't get it to work.<BR>Enabling custom paging alone will not make any faster to pull 300,000 records from the db to the web server. Pretty much all that option does is let you set the .VirtualItemCount programmatically. Whether you use ASP or ASP.NET, or whatever all take any SQL with a select * from tablename seriously and actually try to pull all the data. <BR><BR>The trick we have used successfully in 1 Click DB for the past few years (and have now employed for our new .NET WebFormAdapter) goes something like this : <BR><BR>Do _not_ load any more records then you need to display. Adjust your SQL query to use TOP x where x is the numeric position of last record in the query you want to display in the page. Then use the .Fill method of your data adapter to pull only the records you need into a dataset (for classic .ASP use .GETRows for the same effect) and work with that subset for displays. Unfortunately for ASP.NET datagrid, this also mean a bunch of extra fiddling to enable standard paging buttons (we just override them around completely but that is not always necessary depending on your design goals). <BR><BR>One drawback of this is that you will need to issue a separate SELECT COUNT(*) query to get the size of your recordset which requires another SQL Query against the db. For small datasets this makes for a performance penalty but for large datasets it is still _much_ faster to count a few million records than to actually return them to the web server !! <BR><BR>Another thing to note is that this will not save much when trying to view the last page of a 300,000 item recordset, but you can people from doing that with a MaxRecords variable. And of course if you really need to see that last page, you can just reverse the sort and it becomes the first page. <BR><BR>The performance difference between using this technique vs "standard" techniques for large dataset is amazing !!! <BR><BR>I am going to write up a more in depth article on this technique(after the big VS.NET launch party tommorow) since it is so simple and so powerful (for 2million + records it beat a bunch of hand crafted, Stored Procedure based paging methods and every popular commercial ASP Grid component we tried). <BR><BR>Feel free to followup via email for more info on the technique. <BR><BR>regards, <BR><BR>Dave Kawliche <BR>http://AccessHelp.net <BR>http://1ClickDB.com <BR>