ODBC SQLColums is very slow
Date: Tue, 20 Jan 2009 15:05:51 +0000 (UTC)
Message-ID: <Xns9B99A3C0D4521wrgrpde_at_news.albasani.net>
Hello,
I'm using the Oracle ODBC driver to connect to my 10g database, and found the SQLColumns function (that returns a table description similiar to sqlplus's "describe" command) to be terribly slow: It takes about 1.3 seconds per table, even when executed repeatedly for the same table. In sqlplus, "describe" for the same tables returns instantly.
I ran SQLColumns in SQL_TRACE mode and found that it submits the following two queries (GRP_ALLKONF is my table name, a simple table with seven columns):
SELECT /*+ RULE */ COUNT(*)
FROM
ALL_SYNONYMS WHERE DB_LINK IS NOT NULL AND UPPER(SYNONYM_NAME)=
UPPER('GRP_ALLKONF')
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 10.17 15.38 0 212858 0 1
------- ------ -------- ---------- ---------- ---------- ----------
total 3 10.17 15.38 0 212858 0 1
SELECT /*+ RULE */ '',owner,table_name,column_name,0,data_type,
data_precision, decode(data_type, 'DATE',16,'FLOAT',8,'LONG
RAW',2147483647,
'LONG',2147483647,'CLOB',2147483647,'NCLOB',2147483647,'BLOB',2147483647,
'BFILE',2147483647,'CHAR',char_length,'NCHAR',char_length,'VARCHAR2',
char_length,'NVARCHAR2',char_length,'NUMBER',NVL(data_precision+2,40),
data_length), data_scale, 0, decode(nullable, 'Y', 1, 'N', 0), '' , '',
0,
0,decode
(data_type,'CHAR',data_length,'VARCHAR2',data_length,'NVARCHAR2',
data_length,'NCHAR',data_length, 0),column_id, decode(nullable, 'Y',
'YES',
'N', 'NO')
FROM
all_tab_columns WHERE UPPER(TABLE_NAME)=UPPER('GRP_ALLKONF') UNION
SELECT
/*+ RULE */ '',b.owner,b.synonym_name,a.column_name, 0, a.data_type,
a.data_precision, a.data_length, a.data_scale, 0, decode(a.nullable,
'Y', 1,
'N', 0), '' , '', 0, 0, 0, a.column_id, decode(a.nullable, 'Y', 'YES',
'N',
'NO') FROM all_tab_columns a, all_synonyms b WHERE ((a.table_name =
b.table_name and a.owner = b.table_owner) ) AND UPPER(b.synonym_name)=
UPPER('GRP_ALLKONF') ORDER BY 2,3,17
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 6.67 7.48 0 213578 0 7
------- ------ -------- ---------- ---------- ---------- ----------
total 4 6.67 7.48 0 213578 0 7
This time, with tracing turned on, it even took 23 seconds. The normal time for a single SQLColumns call is about 1.3 seconds however.
The database has plenty of RAM and nothing much to do. Everything else is fast and fine.
My database version is 10.2.0.2, and my ODBC driver version is 9.2.0.5.4, but I found SQLColumns to be slow with other combinations of database and driver too.
Any idea what I can do to speed up my calls to SQLColumns?
Thanks for any help
W. Roesler
Received on Tue Jan 20 2009 - 09:05:51 CST