Browsing a large recordset

[Marc Vanzegbroeck]

Hi,

I have a question about browsing a large amount of records with ADO.
I have a client that want to browse a table with lot of records.
The problem is that running the query to get the recordset is taking already 3 .5 seconds.
What I can do is setting a LIMIT op 1000 records, and fetching the next 1000 when I get to the buttom of the browse.

HeidiSQL is doing this by doing

Code: Select all  Expand view

SELECT  * FROM .. ORDER BY .. ASC LIMIT 1000;

and then when reaching the bottom

Code: Select all  Expand view

SELECT  * FROM .. ORDER BY .. ASC LIMIT 1000,1000;

How can I do this, with xbrowse()

[nageswaragunupudi]

XBrowse or some other browse is not the issue.

The second sql statement results in a 2nd recordset different from the 1st recordset.
You can not concatenate both recordsets.
Hope you agree.

Do you plan to replace the 1st record set with 2nd recordset?

While browsing one of the recordsets the user wants to change the sort order. How do you propose to handle this?

If you are first clear on how do you plan to handle ADO recordset issues, then you can fit your idea easily into xbrowse or any other browse.

[nageswaragunupudi]

This is for information.
If you are using recent FWH, you can use FWMYSQL RowSet. You can continue to use ADO for most of the application and use FWHMYSQL rowset in one or two cases.

With rowsets it is possible to read a few records initially and readnext records later. Records read later are appended to the rowset and the rowset is enlarged.

oMySql := mysql_Open( oAdoCnObject )
oRs := oMySql:RowSet( <ctable>, 1000 ) // read first 1000 records

<< browse code >>

At an appropriate time, you can

oRs:ReadNext( 2000 ) // read and append next 2000 records
or
oRs:ReadNext() // read all remaining records and append

oRs:lMore if .t. there are still some records to be read. If oRs:lMore is .f., no more records are remaining to be read.

If you want to read next records when you reach end of file in xbrowse,

oBrw:bPastEof := { || If( oRs:lMore, oRs:ReadNext(500), nil ), oBrw:Refresh() }