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.
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,