At 09:54 PM 01-01-01 -0600, you wrote:

>I have been struggling for the past couple of months attempting to convert
>an existing BDE/Paradox based app over to IBO/InterBase, and have run into
>a major road block that hopefully someone can shed some light on.
>
>The app I am converting has roughly 250 tables and well over 1000
>datasets. The average database size is roughly 2GB and the average number
>of records per table is roughly 200,000. It's a fairly large scale app
>that could really benefit having IB as a back end.
>
>My problem is this, Almost every major form in the app contains shortcut
>keys and toolbar buttons for first, previous, next and last record
>(Similar to a navigator control). The user also has the ability to jump
>directly to a record based on an entered key field value.
>
>Using the BDE & Paradox with TTable components, this feature was fairly
>easy to implement, consumed very little memory and was lightning fast.
>Using IBO & InterBase I cannot seem to achieve the same effect.
>
>I am assuming that a scrollable dataset is required for these features, so
>I use a TIBOQuery. However performing a Last or Locate ends up buffering
>the key data for the entire table. The tables are fairly large and this
>process end up exhausting all the clients memory and causes an enormous
>amount of network traffic.

You have bumped into the key difference between local file service
databases and client/server.

>Please tell me there is some way to scroll around a dataset without having
>to buffer all the key data in the clients memory ? My users will have a
>fit if these features are taken away and will most likely reject the
>upgrade in favor of the old BDE/Paradox version.

If it's an absolute requirement that your users be able to browse 200,000
rows, I think you are stuck with Paradox and TTables. C/S interfaces need
a different approach altogether from hooking up a table to a grid and
telling users, "go fetch". TTable makes this a very tempting UI solution
(from the programmer's perspective) but with client/server, you have to
"think thin".

C/S involves finding out what your user wants and telling the database "go
fetch". Therefore, your design mission, should you choose to accept it, is
to abstract what users actually want to do when they scroll a TTable, and
design an interface for searching. If you do it right, it will be
lightning fast; but your users **will** have to part from the spreadsheet
interface.

You can get along quite well with IBO's TDataset-based components but
you'll write a lot of code to achieve it; for a project of the scale you
have, you will save hundreds of hours by planning to go straight to native
IBO, which has complete encapsulation of the behaviour you need for a C/S
client and a ton of "smarts" in the client buffers for optimising both
client and server-side activity.

>Also, is there any way in IB to create a case insensitive index like in
>the BDE ?

Case-insensitive searching in IB needs indexed case-insensitive proxy
columns to get any sort of performance. The usual approach to this is to
create and index these columns and populate them at insert and update via
before insert and before update triggers. IBO then lets you call on these
proxy columns transparently for c/i searches. It's doable with the TIBO*
components, by accessing some attributes of the InternalDataset; it's much
easier to do with native IBO.

Not what you wanted to hear, I suppose...
Converting from file served data and TTable to server-based data service
and querying just isn't trivial, as you've already discovered. You really
can't make an orange out of an apple. If your app is multi-user, you are
living on borrowed time with a 2 Gig database under file service. If it's
single-user, a change to client/server might not be what's needed.

Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________