ODBC SQLColums is very slow

From: Wolfram Roesler <wr_at_spam.la>
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

Original text of this message