--- In IBObjects@y..., Helen Borrie <helebor@t...> wrote:

> At 06:56 AM 28-11-02 +0000, you wrote:
> >Dear all,
> >
> >I am using IBO4.2Ib on WinXP. The server is Firebird 1.0 stable
> >release version (tried both Linux/ WinXP versions).
> >
> >Connection to server with effective throughput of 512Kbps.
> >
> >When I initially open a IB_Query which only needs to returns a few
> >rows, it takes up to 7 secs. Subsequent open is much faster, within
> >1 to 2 seconds. I used the monitor to find that, in the initial

open,

> >most time is spent on getting metadata and table structure , like:
> >"SELECT S.RDB$FIELD_NAME ..."
>
> OK, we are looking at two things here. Looking at the second one

first,

> the "SELECT S.RDB$FIELD_NAME ..." will happen once per CONNECTION

if you

> have local schema caching enabled. You can turn this off in your

client by

> setting the SchemaCacheDir to nil. In the course of your session,

you will

> lose the benefit of having IBO read your metadata locally; but it

looks as

> if your user might be connecting and disconnecting frequently - in

which

> case the schema cache is more of a burden than a benefit.
>
> Now, the other one is the Prepare sequence. If the statement

specification

> doesn't change, this should occur only on the very first call to

Open. If

> you are replacing the SQL of the query every time you open it, then

Prepare

> is happening EVERY time. Two seconds is a VERY long time for a

prepared

> query to return only a few rows.
>
>
> >Can I make the Query to skip this? Becuase sometimes I only want to
> >run a very simple query, I don't need these metadata.
>
> The statement ALWAYS needs the metadata - that is what the Prepare

is

> for. If possible, you should architect your queries so that they

get

> prepared once and remain prepared. That means using parameters

wherever

> possible.
>
> And, of course, you should design your metadata so that they help

to speed

> up your queries, not slow them down. For example, duplicate

indexes on

> primary and foreign keys are performance-killers; as are indexes

or

> foreign keys on lookup columns which have a small distribution of

possible

> values throughout the table; as are ordering or joining columns

which have

> better distribution but which have no indexes at all, or bad

indexes.

>
> So you need to tell us more about 1) the queries and 2) the

handlers that

> you are using to open them.
>
> Helen

The schemacachedir was already disabled (blank). In the contrary,
after I enabled it, the initial open speed increased as it doesn't
load the metadata on subsequent connections. However, there are
errors when running in the IDE, it looks like the IBO$SCHEMA_VERSION
table is missing. I cannot find the details of this table, so I
cannot create it manually (I tried to call
SchemaCache.CheckSchemaVersionTable, but it failed). Attached is my
monitor output:

Call Ib_Connection1.Connect
/*---
CONNECT DATABASE ora8i.minidns.net:/u/fbdata/sms.fdb
DB_HANDLE = 4150260

SECONDS = 0.500
----*/

Call IB_Query1.Open:
/*---
DATABASE INFO
DB_HANDLE = 4150260

SECONDS = 0.188
----*/
/*---
START TRANSACTION
DB HANDLE COUNT 1
TR_HANDLE = 4150092

SECONDS = 0.062
----*/
/*---
START TRANSACTION
DB HANDLE COUNT 1
TR_HANDLE = 4149636

SECONDS = 0.062
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 4149636
STMT_HANDLE = 4149708

SELECT SCHEMA_ITEM || '=' || VERSION_NUMBER
FROM IBO$SCHEMA_VERSION
ORDER BY 1 ASC

FIELDS = [ Version 1 SQLd 0 SQLn 30 ]

SECONDS = 0.094

ERRCODE = 335544569
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 4149636
STMT_HANDLE = 4149708

SELECT SCHEMA_ITEM || '=' || VERSION_NUMBER
FROM IBO$SCHEMA_VERSION
ORDER BY 1 ASC

FIELDS = [ Version 1 SQLd 0 SQLn 30 ]

SECONDS = 0.125

ERRCODE = 335544569
----*/
/*---
INTERPRETE BUFFER =

ERRCODE = 17
----*/
/*---
INTERPRETE BUFFER = Dynamic SQL Error

ERRCODE = 21
----*/
/*---
INTERPRETE BUFFER = SQL error code = -204

ERRCODE = 13
----*/
/*---
INTERPRETE BUFFER = Table unknown

ERRCODE = 18
----*/
/*---
INTERPRETE BUFFER = IBO$SCHEMA_VERSION

ERRCODE = 20
----*/
/*---
INTERPRETE BUFFER = At line 2, column 6.

ERRCODE = -1
----*/
/*---
COMMIT
TR_HANDLE = 4149636

SECONDS = 0.219
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 4150092
STMT_HANDLE = 4149900

SELECT ID, NAME FROM BRAND

PLAN (BRAND NATURAL)

FIELDS = [ Version 1 SQLd 2 SQLn 30
BRAND.ID = <NIL>
BRAND.NAME = <NIL> ]

SECONDS = 5.875
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 4150092
STMT_HANDLE = 4149900
PARAMS = [ ]

SECONDS = 0.422
----*/

Many thanks to your superb support.