>I'm in the middle of a project which has been using BDE access for all
>queries, updates, etc and we need to look at ways of improving
>performance. We have a number of instances where a cascading query is
>made on the record set (about 40K records now, but could grow in the
>next few years to several hundred thousand), i.e. keep querying a
>parent_id of a record to create a tree view or listing.

Chuck,
IBO won't help you at all if you want the IB server to run faster. And if
you're saying that you transfer 40K+ records to do some modifications on
the client this is severely wrong if done interactively with users waiting
(but I guess IBO could be a great help in 'hiding' your design problems).

If you need to do modifications to all records, do it in one UPDATE query
or write a stored procedure. I don't know how familiar you are with
client/server databases and maybe you know this already, but unless you're
dealing with a very small table where speed is not an issue you should
always retrieve only a small subset of the records of the table to the client.

>Recently worked on a portion of the code that goes through the file to
>do this and to do all 35K items, it took 5-10 minutes, depending what
>other applications were using memory.

The IB server can be pretty fast - unless each record is big, it should be
able to process several thousand records per second (on the server,
transfering to a client takes longer).

>My experience has been with Visual FoxPro which would easily be 100x faster
>than this.

I must admit not knowing FoxPro, but isn't that a desktop database? If so,
I'm not surprised - if working directly on a table as a single user and
transferring that approach to InterBase. But add multiple users and/or
transactions, large tables and a properly designed program with narrow
select criteria (the main pitfall for desktop database developers) to a
well-defined database and no desktop database can compare to IB.

>Does anyone have experience in comparing a IBO vs a BDE access speeds to
>give me a broad idea of what kind of speed increases we can get by
>replacing the BDE components with IBO?

IBO is a lot better than the BDE in almost every area. The BDE is designed
for desktop databases, whereas IBO takes advantage of IB's capabilities.
I'd say the greatest benefit is there being a lot less unexplainable errors
with IBO and that program development is a lot faster using the native
components. You do get a speed benefit as well, but I guess your main
problem probably is your design which you probably should improve
regardless of whether you use IBO/BDE/IBX/FIB.

HTH,
Set