Hello Rhett,

my system has similar complexities to your's (lot's if tables,
stored procedures, triggers etc), except my users can't add
metadata structures.

I think what I will do is write a little utility app that will
update the schema cache when I install a software release, so
that there will be no impact on users.

Regards


Peter



-----Original Message-----
From: Rhett Rodewald [mailto:rhett.rodewald@...]
Sent: Saturday 28 June 2003 9:43 AM
To: ibobjects@yahoogroups.com
Subject: [IBO] Re: FW: IBO seems slower for some things compared with
the BDE



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@...>
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
> > > > > > > >




___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/