MetaData Query Performance Problem

[medalex]

Hello,

we have upgraded out dbExpress driver from old version (2003 year) to the latest one. After upgrade existing application became slower than it was with old drivers.
We use Delphi 7. Also I have converted TSQlConnection to TCRSQLconnection. Using DBMonitor helped to find that most time consuming sql queries are MetaData ones.

For example:

Code: Select all

 SELECT ROWNUM RECNO,
         '<NULL>' CATALOG_NAME,
         I.OWNER SCHEMA_NAME,
         I.TABLE_NAME,
         I.INDEX_NAME INDEX_NAME,
         IC.COLUMN_NAME,
         IC.COLUMN_POSITION,
         C.PKEY_NAME,
         NVL (C.KEY_TYPE, DECODE (I.UNIQUENESS, 'UNIQUE', 2, 1)) INDEX_TYPE,
         'A' SORT_ORDER,
         '<NULL>' FILTER
    FROM (SELECT INDEX_NAME, COLUMN_NAME, COLUMN_POSITION
            FROM SYS.ALL_IND_COLUMNS
           WHERE TABLE_NAME = 'LABOR_ANALYYS' AND INDEX_OWNER = 'MARKOTEST') IC,
         (SELECT OWNER,
                 TABLE_NAME,
                 INDEX_NAME,
                 UNIQUENESS
            FROM SYS.ALL_INDEXES
           WHERE TABLE_NAME = 'LABOR_ANALYYS' AND OWNER = 'MARKOTEST') I,
         (SELECT OWNER,
                 TABLE_NAME,
                 INDEX_NAME,
                 DECODE (CONSTRAINT_TYPE, 'P', CONSTRAINT_NAME, NULL) PKEY_NAME,
                 DECODE (CONSTRAINT_TYPE,
                         'P', 6,
                         DECODE (CONSTRAINT_TYPE, 'U', 2, 1))
                    KEY_TYPE
            FROM SYS.ALL_CONSTRAINTS
           WHERE TABLE_NAME = 'LABOR_ANALYYS' AND OWNER = 'MARKOTEST') C
   WHERE I.INDEX_NAME = IC.INDEX_NAME AND C.INDEX_NAME(+) = IC.INDEX_NAME
ORDER BY NVL (C.KEY_TYPE, DECODE (I.UNIQUENESS, 'UNIQUE', 2, 1)) DESC,
         I.INDEX_NAME,
         IC.COLUMN_POSITION

Duration: 0.016

Why is it needful to get every time metada for the same query? Could it be done only once or improved?

Thank you in advance,

Alexandr

[AlexP]

Hello,

This query is called in our override method "getIndices" that is called from a standard dbExpress method "TCustomSQLDataSet.OpenSchema". This call occurs only in the case when the GetMetaData option is set to True, and the NoMetaData option - to False in a DataSet. To avoid calling of this query, you should change the values of these options

[medalex]

Thank you very much.
Finally I have turned these queries off.

Thanks.