Subject Re: FW: IBO seems slower for some things compared with the BDE
Author stanw1950
We have had the app and the schema directory on a shared network
drive for over 6 months and have had no problems. The only delay is
when the schema files are deleted and are recreated for the first
user. That is almost always me as I take down the app, delete the
files, start the app again, and wait a few extra seconds. This I only
do maybe once every couple of weeks since our metadata does not
change all that much. I can change the live metadata (like adding a
new stored procedure) with the users still in the app and wait until
the end of the day to update the schema files. Without the schema
files we would probably still be using the BDE (although we still are
until I fully convert the application - we choose to convert a module
at a time instead of everything at once).

Stan Walker






--- In IBObjects@yahoogroups.com, Rhett Rodewald
<rhett.rodewald@e...> wrote:
>
> Peter,
>
> Here are my observations:
>
> Using IBO components significantly slows load time. IB_ components
do NOT.
> People who are writing "clean" apps with only IB_ components, or
who only
> have a small number of tables in their database tend not to realize
this.
>
> IBO comps seems to require reading the *entire* schema of the
database,
> even if you only ever open a single table. If your database is
large (mine
> currently has 1450 tables, 565 Triggers, and 586 generators) this
can take
> a LONG time. (Approx 3 min in my case -- 100 Mb/s network, 1 Gig
backbone,
> 2 GHz Linux server, Firebird 1.0.) See below for my test results --
notice
> the 177 second execution time. (the ... is where I snipped out a
bit,
> otherwise that is straight from the monitor.)
>
> I must admit, though, I do not understand why this takes so long as
I have
> tried "hand-executing" this query and it returns almost instantly --

> perhaps it is being run mutltiple times for a range of
parameters??? It
> doesn't even take very long to read the entire table from the
server (I
> have 7454 records in the RDB$Index_Segments table) if I read it as
a single
> query.
>
> Anyway, setting SchemaCacheDir helps significantly, however, the
*first*
> load will be as slow as before. I am running with SchemaCacheDir
set to a
> shared network drive -- so far it seems to work, but we're still in
the
> testing stages -- and I've gotten no guarantees that that is
acceptable or
> supported.
>
> Any time your schema data changes, expect the same delay while the
> SchemaCache is rebuilt.
>
> I have added my own "UpdateSchema" system (using generators) -- to
try to
> keep the SchemaCache up to date for all clients when
modifying/adding
> tables/indexes/etc on the fly since I'm using a shared network
drive, and
> my application modifies schema data all the time. When I get time,
I will
> re-visit this to try to optimize known updates without the tedious
delays.
>
> If I get this working well, I'll send it to Jason to include in the
next
> release. E-mail me privately if you want what I've got so far.
>
> --Rhett
>
> ------------------------------------------------------------------
>
> /*---
> [ 9/12/2002 4:45:19 PM ]
> CONNECT DATABASE firebird:/usr/data/ebsdata.gdb
> DB_HANDLE = 16866892
> SECONDS = 0.379
> ----*
> /*---
> [ 9/12/2002 4:45:22 PM ]
> DATABASE INFO
> DB_HANDLE = 16866892
> SECONDS = 0.015
> ----*/
> /*---
> [ 9/12/2002 4:45:22 PM ]
> START TRANSACTION
> DB HANDLE COUNT 1
> TR_HANDLE = 16866728
> ----*/
> /*---
> [ 9/12/2002 4:45:23 PM ]
> START TRANSACTION
> DB HANDLE COUNT 1
> TR_HANDLE = 16866272
> ----*/
>
> /*---
> [ 9/12/2002 4:45:23 PM ]
> PREPARE STATEMENT
> TR_HANDLE = 16866272
> STMT_HANDLE = 16866344
> SELECT RDB$FIELD_NAME
> FROM RDB$INDEX_SEGMENTS
> WHERE RDB$INDEX_NAME = ? /* Name */ ORDER BY RDB$FIELD_POSITION ASC
> PLAN SORT ((RDB$INDEX_SEGMENTS INDEX (RDB$INDEX_6)))
> FIELDS = [ Version 1 SQLd 1 SQLn 30
> RDB$INDEX_SEGMENTS.RDB$FIELD_NAME = <NIL> ]
> SECONDS = 0.119
> ----*/
>
> ...
>
> /*---
> [ 9/12/2002 4:45:37 PM ]
> EXECUTE STATEMENT
> TR_HANDLE = 16866272
> STMT_HANDLE = 16866344
> PARAMS = [ Version 1 SQLd 1 SQLn 1
> [NAME] = 'RDB$PRIMARY1 ' ]
> SECONDS = 177.084
> ----*/
>
> ------------------------------------------------------------------
>
>
>
> From: "stanw1950" <stanw@e...>
> If you are using a TIBODatabase component try using the
SchemaCacheDir
> property. This greatly improved the initial performance for us.
When the
> schema cache files do not exist, the first user in will cause them
to be
> created. When the metadata for your database changes just delete
the cache
> files and they will be recreated automatically again.
> I put it in the same directory where the application is in
a "schema"
> directory (and there will be another directory with the database
name in
> this directory):
> MyDb.SchemaCacheDir := ExtractFilePath(Application.ExeName)
+ 'Schema';
>
> Stan Walker
>
>
>
> --- In IBObjects@yahoogroups.com, "Peter McLeod"
<PeterMcLeod@p...> wrote:
> > > I have an application which I have just ported from the BDE to
> IBO. I then noticed that the application took longer to load
compared with
> the same application using the BDE (48 seconds compared to 6
seconds).
> > > > In order to quantify where the differences were, I then used
> Sleuth stopwatch to time the two programs. During this time I
noticed that
> IBO seemed to be slow as it was retrieving information about the
database
> (according to Codewatch the time differences coudld be attributable
to the
> functions/procedures SchemaIndexDefinitions, SchemaDefaultedCols
and
> SchemaProcedureInfo, SchemaPrimaryKeyInfo from IB_Schema.pas, and
> SysGetBlobData from IB_Session).
> > > > On the whole examing the time taken for other processes was
> faster than using the BDE, it was the retrieval of the database
information
> which slowed the whole thing down.
> > > > Does anyone know why IBO needs to retrieve this information,
or
> could it be in the porting I have a BDE setting which is causing
things to
> slow down?
> > > > Any assistance would be greatly appreciated.
> > > > Regards
> > > > > Peter Mc Leod
> > > > > > > > >