MetaData Query Performance Problem

MetaData Query Performance Problem

Postby medalex » Fri 04 May 2012 10:59

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
medalex
 
Posts: 6
Joined: Fri 09 Mar 2012 09:52

Re: MetaData Query Performance Problem

Postby AlexP » Fri 04 May 2012 13:58

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
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: MetaData Query Performance Problem

Postby medalex » Fri 11 May 2012 07:37

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

Thanks.
medalex
 
Posts: 6
Joined: Fri 09 Mar 2012 09:52


Return to dbExpress driver for Oracle