Subject | Re: FW: IBO seems slower for some things compared with the BDE |
---|---|
Author | Rhett Rodewald |
Post date | 2003-06-28T05:00:15Z |
Jason,
Thanks for your response.
- Yes, as noted, I have a ridiculous number (1450+) of tables.
- I have traced the problem to the "SchemaIndexDefinitions" procedure.
I'm not claiming that there is a faster way to extract this information,
and the existing SchemaCache system solves most of the delays, however, I
see several possible work-arounds:
1) Update the SchemaCache as schema changes are made directly -- without
requerying the server. (Obviously this has the potential to get our of
sync, so a complete re-
build must always be an option.)
I have added some new code to track schema changes using generators -- but
it doesn't adjust the cachefiles directly, and it isn't integrated into IBO
yet, so I have to make a seperate procedure call after every update and
tell it which items I've updated.
2) Only query/cache data as needed. (i.e. My app uses about 30 "master"
tables, and a few "sets of 5" client tables at a time. I never come close
to using all 1400+ tables at once.) In general, this is how a "cache" is
supposed to work, is it not? Prevent re-fetching often used data, not
necessarily by "caching" EVERYTHING. If IBO retrieved the
"SchemaIndexDefinitions" on a per-table, as needed basis, it would help (my
app anyway) quite a bit.
I realize that I have an "unusual" application, and I will work on adding
this functionality when I have time and submit it to you for inclusion in
the main source tree.
P.S. Just so everyone doesn't think I've lost my mind with 1400+ tables,
allow me to explain. Other than my "master" tables, client tables have
flexible structures and their "extra" fields are accessed with an internal
script language as needed. Generally "client" tables have a partially
fixed structure, but extra fields are added all the time by my users when
setting up each client table.
And, yes, I am aware of how difficult it is to build an app that allows
end users to modify table structures. Such functions are filtered through
my program's interface that prevents them from deleting "base" fields, and
has lots of code to check consistency etc. On the other had, it beats
working on merely a "boring database app" anyday.
I could perhaps prevent some of the SchemaCache problems by seperating out
each set of "client" tables into seperate databases, (instead of merely
seperate tables) but that would be asking for other problems. (like
managing 300+ different database files) Hmmm.... maybe.
--Rhett
On Sat, 28 Jun 2003 00:29:37 +0000 (UTC), IB Objects
<jwharton@...> wrote:
I suggest that your database must have a lot of tables/fields/etc and the
queries I have on the metadata are not so quick since there are no default
indexes on metadata tables.
Please examine the queries I use in the SQL trace monitor and see if some
of
them are performing poorly. Then, once those are identified, try creating
some indexes that would allow the server to perform those queries much
faster.
It would also be useful to make a TI on this so that we can assist others
who have larger metatada definitions in their databases.
Jason Wharton
___________________________________________________________________________
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/
Thanks for your response.
- Yes, as noted, I have a ridiculous number (1450+) of tables.
- I have traced the problem to the "SchemaIndexDefinitions" procedure.
I'm not claiming that there is a faster way to extract this information,
and the existing SchemaCache system solves most of the delays, however, I
see several possible work-arounds:
1) Update the SchemaCache as schema changes are made directly -- without
requerying the server. (Obviously this has the potential to get our of
sync, so a complete re-
build must always be an option.)
I have added some new code to track schema changes using generators -- but
it doesn't adjust the cachefiles directly, and it isn't integrated into IBO
yet, so I have to make a seperate procedure call after every update and
tell it which items I've updated.
2) Only query/cache data as needed. (i.e. My app uses about 30 "master"
tables, and a few "sets of 5" client tables at a time. I never come close
to using all 1400+ tables at once.) In general, this is how a "cache" is
supposed to work, is it not? Prevent re-fetching often used data, not
necessarily by "caching" EVERYTHING. If IBO retrieved the
"SchemaIndexDefinitions" on a per-table, as needed basis, it would help (my
app anyway) quite a bit.
I realize that I have an "unusual" application, and I will work on adding
this functionality when I have time and submit it to you for inclusion in
the main source tree.
P.S. Just so everyone doesn't think I've lost my mind with 1400+ tables,
allow me to explain. Other than my "master" tables, client tables have
flexible structures and their "extra" fields are accessed with an internal
script language as needed. Generally "client" tables have a partially
fixed structure, but extra fields are added all the time by my users when
setting up each client table.
And, yes, I am aware of how difficult it is to build an app that allows
end users to modify table structures. Such functions are filtered through
my program's interface that prevents them from deleting "base" fields, and
has lots of code to check consistency etc. On the other had, it beats
working on merely a "boring database app" anyday.
I could perhaps prevent some of the SchemaCache problems by seperating out
each set of "client" tables into seperate databases, (instead of merely
seperate tables) but that would be asking for other problems. (like
managing 300+ different database files) Hmmm.... maybe.
--Rhett
On Sat, 28 Jun 2003 00:29:37 +0000 (UTC), IB Objects
<jwharton@...> wrote:
I suggest that your database must have a lot of tables/fields/etc and the
queries I have on the metadata are not so quick since there are no default
indexes on metadata tables.
Please examine the queries I use in the SQL trace monitor and see if some
of
them are performing poorly. Then, once those are identified, try creating
some indexes that would allow the server to perform those queries much
faster.
It would also be useful to make a TI on this so that we can assist others
who have larger metatada definitions in their databases.
Jason Wharton
___________________________________________________________________________
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/