Hello,
maybe the subject caught your eye ...
Yes, I know how to do a browse with SQLExpress and Xbase++, no problem, but
- let me expand on this one.
I have developed an application with the usual interface (and Foxbase
dbf's). If the user is on an entry field that is validated against a
database, he can start a browse that lists the possible values (example: a
customer code).
Under Xbase++ XbpBrowse(), only a subset of the table is read. If the user
scrolls up or down, the next subset is read.
For several reason I think of re-writing the application, using a SQL
server instead of Foxbase tables.
How do I do a browse with SQL? I could create a SQLdataset() or SQLselect()
that includes all records. I did a test on my PC with MySQL installed
locally, reading a table with one million records. It took about two
minutes until the browse was displayed. I think this is not acceptable,
either for the user to wait that long, but also for the PC which needs to
manage that lot of information, but also for the server if a greater number
of users is doing that kind of request.
It is possible to limit the dataset, but how do I deal with the scrolling
up/scrolling down?
Or better yet, how do YOU do it? Is someone willing to share his ideas with
me (and the other ones reading this thread)?
And - hopefully - it should be a solution that is not limited to one SQL
server, using a special version of SELECT or something like that.

Kind regards,
Georg S. Lorrig

As an aside note you should not use SELECT *
Specify only the fields (columns) needed for the browse. This can speed
things up a lot since only the required fields are moved across the network.

If you really need to browse such a large result set, the
solution is to use SQLSelect with either a dynamic or keyset driven cursor.

However, I do not recomend browsing such a large result set. I suggest that
you redesign the UI and browse data in smaller chunks.

If I had time, I'd knock up an SqlDacPagedDataStore for use with SqlExpress,
because I think that would prove a very useful tool to a great many people.
Unfortunately I am so busy at the moment that I can't really say for certain
when I'd be able to get around to it.

When I get some free time, and if no-one has beaten me to it, I'll certainly
have a go.

Ok, I've written one :-)

Don't get excited just yet, it'll need a bit of testing and debugging, but
it was (generally) much simpler than I thought, knocking in at around 240
lines of code. I have to add the methods scrollUp, scrollDown and scrollTo,
but won't do that until the rest of it is working.

When I get my next free 10 minutes, I'll start debugging it :-)

It is possible to limit the dataset, but how do I deal with the scrolling
up/scrolling down?

Hi,
Have a look at the thread "Large datasets" which is a discussion on your topic.  I think the conclusion is summarized to Boris' reply....
> solution is to use SQLSelect with either a dynamic or keyset driven cursor.
>
> However, I do not recomend browsing such a large result set. I suggest that
> you redesign the UI and browse data in smaller chunks.
... or Phil Ide's idea....
> because I think that would prove a very useful tool to a great many people.
> Unfortunately I am so busy at the moment that I can't really say for > certain
> when I'd be able to get around to it.
>
> When I get some free time, and if no-one has beaten me to it, I'll > certainly
> have a go.
...I quite agree that this would be a VERY usefull too to a great many people.  A little later Phil wrote...
>
> Don't get excited just yet, it'll need a bit of testing and debugging, but
> it was (generally) much simpler than I thought, knocking in at around 240
> lines of code. I have to add the methods scrollUp, scrollDown and scrollTo,
> but won't do that until the rest of it is working.
>
> When I get my next free 10 minutes, I'll start debugging it :-)
I don't think he has had the time for that yet.  I have posted a couple of reminders for him... (Phil, are you there?)
Anyways, I started with Xbase this summer and have not yet any good idea on how to handle large cursors.  With "large cursors" in this context I presume we are using SqlSelect (serverside) cursors.
Because of time preassure I have not had the time to experiment much, so until I get a better idea from here/by myself I use a " 'Fetch previous/next XX records'-buttons"-solution.  Searching goes through a temporary search/lookup-cursor from where I get a unique key (:aPrimaryKey) which I then search for in the primary cursor. If it's not there, I change the oCurs:SqlString accordingly and do a oCurs:Refresh().  Cumbersome, but the best I have had the time to come up with so far.
I have not yet implemented this solution into my app yet (at the moment I retrieve all rows, not subsets), but I've tested it and it works pretty fast.  What I have implemented as a result from this testing is sorting SqlSelect cursors in browses.  When the user clicks on a browse heading the cursor gets sorted by that column by changing the oCurs:SqlString as mentioned above (in this case the only change in the :SqlString is the "... ORDER BY ..." part).
I also have a generic browse function and all calls to SqlDataSet/SqlSelect goes through one semi-generic function.  This makes it a snap to change any behaviour for the whole app.
> up/scrolling down?
Yes, this is why I have not implemented it yet.  When scrolling in my (test) browse and you reach oCurs:Bof()/Eof() you have to push either the "Fetch next XX records" button or the "Fetch previous XX records" button.  When I get the time I will make this fetching automatic.  This will give the user the impression of scrolling through a full cursor, not just a subset of a couple of houndred rows.  I think I sat the subset limit to 200 rows/records, and that works very fast in my test environment which (in a separate test) also included a 10 m/bit network.
I hope many people give their oppinion on this!
Best regards,
Mahanimann

Hello,

maybe the subject caught your eye ...

Yes, I know how to do a browse with SQLExpress and Xbase++, no problem, but
- let me expand on this one.

I have developed an application with the usual interface (and Foxbase
dbf's). If the user is on an entry field that is validated against a
database, he can start a browse that lists the possible values (example: a
customer code).

Under Xbase++ XbpBrowse(), only a subset of the table is read. If the user
scrolls up or down, the next subset is read.

For several reason I think of re-writing the application, using a SQL
server instead of Foxbase tables.

How do I do a browse with SQL? I could create a SQLdataset() or SQLselect()
that includes all records. I did a test on my PC with MySQL installed
locally, reading a table with one million records. It took about two
minutes until the browse was displayed. I think this is not acceptable,
either for the user to wait that long, but also for the PC which needs to
manage that lot of information, but also for the server if a greater number
of users is doing that kind of request.

It is possible to limit the dataset, but how do I deal with the scrolling
up/scrolling down?

Or better yet, how do YOU do it? Is someone willing to share his ideas with
me (and the other ones reading this thread)?

And - hopefully - it should be a solution that is not limited to one SQL
server, using a special version of SELECT or something like that.


Kind regards,

Georg S. Lorrig