Subject | Re: [IBO] TIBOQuery to TClientDataSet really slow |
---|---|
Author | Helen Borrie |
Post date | 2003-09-17T15:41:15Z |
At 12:19 AM 18/09/2003 +1000, you wrote:
What you describe here is the connection getting the metadata of your
extraordinarily large (in terms of objects) database. As someone else
suggested, you can avoid having this happen every time on connection by
enabling schema caching. After that, as long as the metadata doesn't
change, the schema querying won't occur on each connection. Properly set
up, schema caching stores a local cache (just a text file) containing info
which IBO uses instead of querying metadata across the wire into the memory
cache.
The metadata version is stored in a table IBO$SCHEMA_VERSION on the server
with a version number. IBO creates this table automatically if it doesn't
exist, but it doesn't put any data into it - that is up to the DBA who
makes the metadata changes. Your users will need to be granted SELECT
rights on this table.
The connection object verifies once per connection that the local cache has
the same version number as the latest on the server. If it's not, the
connection object regenerates the cache, which will be a fairly protracted
process for your database. It's perfectly OK to deploy a pre-built cache
along with your client applications. Read up the help on the
SchemaCacheDir property.
Helen
>We're using integer primary keys for record-uniqueness. I can reproduceNo, not redundantly. IBO makes great use of the metadata.
>the problem with a simple "select * from table" - it can be any table.
>
>Also, if I set the TClientDataSet's PacketRecords property to 0 (only
>retrieve metadata) it is still slow. It seems to be copying the
>structure from the TIBOQuery to the TClientDataSet that causes the
>problem.
>
>Looking at the output in IB_MonitorDialog it seems to (reundantly?)
>enumerate all stored procedures, and indexes first (that's over 1500It's storing them into a memory cache.
>items) - that takes 3 seconds.
>Preparing the actual query is fast (0.010 sec).[long posting followed]
>
>It seems to spend the next five minutes doing the following;
What you describe here is the connection getting the metadata of your
extraordinarily large (in terms of objects) database. As someone else
suggested, you can avoid having this happen every time on connection by
enabling schema caching. After that, as long as the metadata doesn't
change, the schema querying won't occur on each connection. Properly set
up, schema caching stores a local cache (just a text file) containing info
which IBO uses instead of querying metadata across the wire into the memory
cache.
The metadata version is stored in a table IBO$SCHEMA_VERSION on the server
with a version number. IBO creates this table automatically if it doesn't
exist, but it doesn't put any data into it - that is up to the DBA who
makes the metadata changes. Your users will need to be granted SELECT
rights on this table.
The connection object verifies once per connection that the local cache has
the same version number as the latest on the server. If it's not, the
connection object regenerates the cache, which will be a fairly protracted
process for your database. It's perfectly OK to deploy a pre-built cache
along with your client applications. Read up the help on the
SchemaCacheDir property.
Helen