Hi,

At June 1, 2005, 13:52, Enrico Raviglione wrote:

> i have used IBO Monitor on my example and i have extracted some parts:

> /*---
> [ 01/06/2005 18.00.12 ]
> PREPARE STATEMENT
> TR_HANDLE = 16871616
> STMT_HANDLE = 16871428

> SELECT I.RDB$RELATION_NAME
> , I.RDB$INDEX_NAME
> , I.RDB$UNIQUE_FLAG
> , I.RDB$INDEX_TYPE
> , I.RDB$FOREIGN_KEY
> , ( SELECT C.RDB$CONSTRAINT_NAME
> FROM RDB$RELATION_CONSTRAINTS C
> WHERE C.RDB$INDEX_NAME = I.RDB$INDEX_NAME ) AS CNST
> , ( SELECT R.RDB$UPDATE_RULE
> FROM RDB$REF_CONSTRAINTS R
> WHERE R.RDB$CONSTRAINT_NAME =
> ( SELECT C.RDB$CONSTRAINT_NAME
> FROM RDB$RELATION_CONSTRAINTS C
> WHERE C.RDB$INDEX_NAME = I.RDB$INDEX_NAME )) AS UR
> , ( SELECT R.RDB$DELETE_RULE
> FROM RDB$REF_CONSTRAINTS R
> WHERE R.RDB$CONSTRAINT_NAME =
> ( SELECT C.RDB$CONSTRAINT_NAME
> FROM RDB$RELATION_CONSTRAINTS C
> WHERE C.RDB$INDEX_NAME = I.RDB$INDEX_NAME )) AS DR
> FROM RDB$INDICES I
> WHERE NOT I.RDB$RELATION_NAME STARTING WITH 'RDB$'

> PLAN (C INDEX (RDB$INDEX_43))
> PLAN (R INDEX (RDB$INDEX_13))
> PLAN (C INDEX (RDB$INDEX_43))
> PLAN (R INDEX (RDB$INDEX_13))
> PLAN (C INDEX (RDB$INDEX_43))
> PLAN (I NATURAL)

====>>> This query seem to be the problem !!! <<====

> PLAN (I NATURAL) here the index are not used because the expression are
> where NOT I.RDB$RELATION_NAME starting with 'RDB$'

It wouldn't make much difference even if it would be using an index.
Just do a comparison between the number of rows where
I.RDB$RELATION_NAME starts with "RBD$" compared to those that don't.
I compared with one of my databases that have 221 tables, and the
result was as follows:
44 rows that starts with "RDB$"
592 rows that don't start with "RDB$"

So as you can see by my results, using an index wouldn't help. But,
if it's only a subset of the returned result set IBO needs, then maybe
the query could be optimized to only fetch those rows. I can't
comment any further, because I haven't taken a look at IBO's source
code to see what it does with this result set.

> % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % %
> % % % % % % % % %

> /*---
> [ 01/06/2005 18.00.17 ]
> PREPARE STATEMENT
> TR_HANDLE = 16871616
> STMT_HANDLE = 16871672

> SELECT RDB$PROCEDURE_NAME
> FROM RDB$PROCEDURES
> ORDER BY RDB$PROCEDURE_NAME ASC

> PLAN (RDB$PROCEDURES ORDER RDB$INDEX_21)

====>>> This query seem to be ok <<====

> % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % %
> % % % % % % % % %

> /*---
> [ 01/06/2005 18.00.17 ]
> PREPARE STATEMENT
> TR_HANDLE = 16871616
> STMT_HANDLE = 16871672

> SELECT RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$FIELD_SOURCE
> FROM RDB$RELATION_FIELDS R
> WHERE ( RDB$SYSTEM_FLAG <> 1 OR RDB$SYSTEM_FLAG IS NULL )
> AND NOT RDB$RELATION_NAME STARTING 'RDB$'
> AND NOT RDB$FIELD_NAME STARTING 'RDB$'
> AND NOT RDB$FIELD_SOURCE STARTING 'RDB$'
> ORDER BY 1 ASC
> , 2 ASC

> PLAN SORT ((R NATURAL))

====>>> This query are not optimized but don't seem to be a big problem.

> <<====

This query fetches the fields that have a (user defined) domain. If
FieldEntryType=[] is used, then why would IBO still fetch this list?
(As stated before I haven't yet taken the time to look in the code)

> % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % %
> % % % % % % % % %

> /*---
> [ 01/06/2005 18.00.18 ]
> PREPARE STATEMENT
> TR_HANDLE = 16871616
> STMT_HANDLE = 16871672

> SELECT R.RDB$FIELD_NAME
> , R.RDB$RELATION_NAME
> FROM RDB$RELATION_FIELDS R, RDB$FIELDS F
> WHERE R.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME
> AND F.RDB$COMPUTED_SOURCE IS NOT NULL
> AND NOT R.RDB$RELATION_NAME STARTING WITH 'RDB$'
> ORDER BY 1 ASC

> PLAN SORT (JOIN (F NATURAL,R INDEX (RDB$INDEX_3)))

====>>> This query are not optimized but don't seem to be a big problem.

> <<====

As long as you don't have too many computed columns.

> But what i can do defining some index on the server.
> The problem are in the query expression then i can nothing with index or
> not ?

If you are using FB 1.5 and the database's ODS is 10.1, then all the
necessary indices have been created. What happens is that "NOT
STARTING" will not use an index, and that's not an IBO issue. The
question here is how IBO can be improved even further. One option
could be to have an option that it would deactivate the parser and
that the queries be sent directly to Firebird? But, the parser
couldn't be 100% deactivated, because of some of IBO's features(i.e.:
the SQLWhereItems property and if the KeyLinksAutoDefine property is
set to True, just to name some features that needs the parser).

--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)