MetaData Query Performance Problem
Posted: 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:
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
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
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