Q+E/ODBC Performance

From: Liam Dwan <lsd_at_quay.ie>
Date: Mon, 4 Jul 1994 12:12:00 GMT
Message-ID: <1994Jul4.121200.28540_at_quay.ie>


Hi there.

We are using Oracle 7 (on HP-UX) with Q+E Multilink/VB.

Up to now we have been using version 1.01 (ie non-ODBC) of Q+E.

When we moved a test system to version 2 of Q+E (ie the ODBC verson) our performance went WAY down especially for (standard) joins. - A query that was returning in 0.26 seconds took 34 seconds!

What seems be happening is that the the ODBC layer supplied by Q+E is doing selects to Oracle system tables to determine the structure of each of the tables involved in the join. An example of such a select (captured via SQL Trace) is as follows;

select a.owner,a.table_name,a.column_name,data_type,data_length,

       data_precision, data_scale, nullable,comments from sys.dba_tab_columns a,sys.dba_col_comments b

where  a.owner=b.owner 
and    a.table_name=b.table_name and a.column_name=b.column_name 
and    a.table_name like'AE_BASIS'order by 1,2,column_id

This takes 17.88 elapsed seconds and the system does this for every table in the join every time the join is executed!

If I use the Oracle ODBC drivers available on Compuserv, the query executes in 2.07 but this is still 10 times slower than the old way. SQL Trace again shows several calls to (different!) system tables such as....

SELECT t.owner, t.table_name
FROM all_catalog t
WHERE 1=1

       AND UPPER(t.table_name) LIKE UPPER('AE_BASIS')
       AND t.table_name NOT LIKE 'ORA_KGL%' ORDER BY 1, 2

for each table involved in the join

I would like to hear from anyone who has had similar experiences/knows what is going on here. Also, has anyone out there used v2 of Q+E with the Oracle ODBC driver in place of the one suppiled with Q+E?

Liam Dwan


Liam Dwan                              _ 
Quay Financial Software,   /          | \             Tel:   +353-1-661 2377
Ferry House,              / ._  __    |  |    _ _     Fax:   +353-1-660 7952
48-53 Lower Mount Street,/_/(_|///    |_/\/\/(// )    Email: lsd_at_quay.ie
Dublin 2.                     
IRELAND                                                                        

----------------------------------------------------------------------------
Received on Mon Jul 04 1994 - 14:12:00 CEST

Original text of this message