Performance Problem: Delphi 2.0 and Oracle

From: Ingi Steinar Ingason <isi_at_itn.is>
Date: 1996/04/09
Message-ID: <316A9F91.3CD4_at_itn.is>#1/1

Hi there!

I am creating an application with Delphi 2.0 (Client server Suite) and Oracle7 using the Oracle SQL link and the BDE.

In the Beta version of Delphi 2.0 opening a table in an ORACLE database for the first time in an application took up to 2 minutes, but since there was some talk about the SQL links would be optimised we did not worry about this. Opening tables with master-detail connections and lookup fields seemed to be especially slow. BUT when we got the final product we saw that the performance had not improved AT ALL! So we put our db into trace mode and found out that the BDE was doing some strange stuff, selecting lots of things from the data dictionary, about constraints, columns, nullable columns and indexes for all the tables, total of five statements for each table opened, not including the real fetching of data. Some of these statements took up to 15 secs since the got into a full table scan on the system tables (sample below). Selecting this data is something one would think the BDE should not need at runtime, and to test this we tried dropping some of the system views these statements used and put instead identical empty tables, for example we dropped sys.dba_constraints, and (surprise, surprise) this did not have any affect on Delphi (EXCEPT for a great improvement in speed).

So my questions are:

• Why is Delphi selecting all this data when it does not need it at all?
• Are there any options I could change in the BDE Config or in some other way to stop the BDE from selecting this data.
• Has anyone had this problem and found a solution for it?
• Does anyone have any ideas he/she would like to share on this matter?
• Why is this only a problem in Delphi 2.0 (the old 16 bit worked fine)?

Any help would be greatly appreciated!

My best,
isi

PS: I tryed defining all the field for a table in the Field Editor but

    still the BDE select all the same information!

                     /\
              /\    /  \
      o      /  \  /\/\/\     Ingi Steinar Ingason
     /\,    /\/\/\/      \    Computer Scientist      isi_at_itn.is
   =|\ |   /      \       \                           tel. +354-568-4380
  (*)/(*) /        \       \  Gagnalind Inc.          fax. +354-568-0191

***************************************************************************SAMPLE SQL STATEMENT:
***************************************************************************select b.r_owner, b.constraint_name, 'MASTER', b.owner, b.table_name, 

b.delete_rule from sys.dba_constraints a, sys.dba_constraints b where a.owner = 'GL_SAGA' and a.table_name = 'S_SHEET' and a.constraint_type = 'P' and a.owner = b.r_owner and a.constraint_name = b.r_constraint_name union select a.owner, a.constraint_name, 'DETAIL', b.owner, b.table_name, a.delete_rule from sys.dba_constraints a, sys.dba_constraints b where a.owner = 'GL_SAGA' and a.table_name = 'S_SHEET' and a.constraint_type = 'R' and a.r_owner = b.owner and a.r_constraint_name = constraint_name order by 1 ASC, 2 ASC

---

Received on Tue Apr 09 1996 - 00:00:00 CEST