Delphi/Oracle Database Perfromance - Order By Problem

From: Edward J Hooper <eddy_at_mcts.demon.co.uk>
Date: 1996/08/16
Message-ID: <gmsTuAA1LIFyEw$x_at_mcts.demon.co.uk>

Would appreciate any comments on the following problem

REPORT ON BORLAND DELPHI/ORACLE PERFORMANCE PROBLEMS Friday, August 16, 1996

1. INTRODUCTION COMM1 a communications package, which uses data stored in paradox tables was required to be converted to use tables stored in an Oracle database running on Workgroup server v7.2 for netware. The source code written in Borland Delphi V1 was altered and early tests proved we could use COMM1 to connect to the tables on Oracle, this led us to believe that conversion was completed. However, further testing using the oracle tables loaded with test data, revealed that we had a performance problem, opening a message browser took 20 seconds, the same operation using paradox tables takes 1 second.
2. DEFINITIONS COMM1 - A communications program, the client software is written in Borland Delphi V1. DOC TABLE - a table that holds the messages for COMM1. BLOB - Binary Large Object, stored in Oracle using the Long Raw data type. DATABASE DESKTOP - a tool provided by Borland, which allows the user to connect to a database and manipulate/query its tables. BDE -Borland Database Engine V2.0, used for communication between the client software and the tables. NETWARE - Novells network operating system, the version used is 4.1. ORACLE - a relational database management system, the version used is Workgroup server V7.2 for netware. SQL*PLUS - an Oracle tool that allows users to run SQL statements directly. SGA -system global area - Oracles shared memory region used to store data and control information for one oracle instance. SHARED POOL - the area in the SGA that contains the data dictionary cache and shared parsed SQL statements.
3. SOLUTIONS TRIED 3.1 File Server Memory We originally thought that there was insufficient memory in our file server (32MB) we increased this to 64MB, with no performance gain from COMM1.

3.2 Database Tuning
Our next assumption on the performance problem was that we had installed and setup the Oracle database incorrectly, i.e. it needed tuning. To investigate this we hired an Oracle expert from Cardiff University, who optimized our Oracle installation for us.

He decided that the performance problem could lie in one of three areas:-

3.2.1.Network communication performance
This was discounted, as if this was the problem then other network applications would be affected, which did not appear to be the case.

3.2.2 Server DBMS Performance
This was the area that was concentrated on first, the focus was on the DOC table, which stores the messages as a BLOB. This table was analysed and database chaining was revealed, all the records from the doc. table which contained fax images were removed. The tables were then exported and imported which removed all database chaining, this resulted in a smaller table stored in a relatively efficient manner. This did produce a performance gain, but not a great deal more than would be expected given the overall reduction in table size. This smaller table still took around 10s to load which is unacceptable for such a small table (<1M).

Further tuning of this dataset produced little or no performance gain, even restructuring the doc. table storage space, and indexes made no difference. It was decided not to restructure the database with a larger block size as it was felt that this would have little effect, especially since the doc. table was no longer chained. It was suggested that further techniques could be employed such as disk striping, but again it was felt this would not cure the underlying problem. Despite initial hopes the results of our efforts were very disappointing to say the least, but this was not so surprising as the Oracle server is relatively efficient given such a small table size regardless of the configuration used.

3.2.3. Client interface performance
Having exhausted the possibilities of DBMS tuning, attention was turned to the client interface and in particular the mechanisms used to access the data stored in the tables. This had not been examined earlier as exactly the same interface is used with paradox tables and no performance problems are evident.

We used a third party Oracle tool that allowed us to view the shared pool in the SGA. We could see that the principal query that accessed the doc. table is in fact a full table scan, a select with no where clause, followed by an order by clause using an index placed on the time_enter column. The full table scan on the doc. table is highly inefficient, and was thought to be the most likely cause of the performance problem as the whole table is being scanned before the user sees any results.

        SELECT NAME_PRE,NAME_ID,NAME_DESCR,TEXTSTART
        TIME_ENTER,INCOMING,SENT,MARKDOC,CATEGORY,LINE_ID
        SENDER_ID,REMARK,D_RIGHTS,U_RIGHTS,READBY,ROWID
        FROM MARK.DOC ORDER BY TIME_ENTER ASC

An examination of the Delphi source revealed that it uses the Borland Ttables class functions rather than those of the Tquery which allow standard sql queries to be issued.

It was generally felt that the relationship between the Borland Ttable data access functions and the Oracle server is not a happy one and Oracle appeared to be satisfying Ttable requests by performing a full table scan.

The general recommendation was to replace all Ttable calls with Tquery calls, as this may then result in the required performance gains. This would mean significant re-coding of COMM1, so we continued to look for a more efficient solution.

3.3 The INTERNET
We browsed the Novell, Borland and Oracle pages on the web looking for an answer to our problem. We found a page on Borland which provided Oracle tips at location http://www.borland.com/Techinfo/sqllinks/text/or atip.txt. This document had a paragraph that stated, if performance is slow you need to add a primary key to the table, we added a primary key to the doc. table, but no performance gain was noted. We concluded that we should be using primary keys on the Oracle tables, but their abscence was not the cause of this problem.

3.4 Borland
Based on the results from the above, we were concerned that our query was returning all the rows from the doc table. We contacted Borland Technical Support. They informed us that the number of rows returned is equal to the number of records being displayed, i.e.it is not returning all the records from the doc table, the BDE ensures that this does not happen. Further discussions with other developers using the BDE, and our own tests using the database desktop show that this would appear to be correct.

3.5 Oracle
Again based on the above we contacted Oracle technical support and they said that Oracle simply uses the SQL statement passed to it by the client service, they had no neat solution to the problem other than the suggestion that we restructure our query.

3.6 32 Bit BDE
We carried out tests using the 32 Bit Borland Database Engine that comes with Delphi V2, this resulted in the same SQL statement, i.e. no improvement in performance.

3.6 Further tests
Using SQL*PLUS we perfomed further tests and felt that the index on the time_enter field in the doc table was not being used, why ?. The index did not seem to be invoked until a where clause was added. (This was confirmed by Oracle technical support.) Our simple query select * from doc order by time_enter took 15s before returning any data. A similar query using an order by on the primary key resulted in the data being returned immediatley.

We looked at the Oracle query optimization mode, you are able to provide hints to oracle in the query, e.g. tell it to use RULE mode and/or an index. Our database is currently set to CHOOSE as the optimization mode, we wanted to change this to use RULE based optimization, as this would allow us access by using the order by clause on indexed columns. After some several failed attempts to change the optimization mode on our database to rule, we decided that this would not work on our current query anyway because we are not using a where clause.

4. CONCLUSION
We believe that the performance problem is caused by the order by clause on the time_enter field in the main query used to access the doc table. Although the time_enter field is indexed, the index does not appear to be being used, instead Oracle is doing a full table scan to fulfill the order by.
We need to find a way to force the index to be used, so that the data is returned immediatley.
We are using delphi ttable components, and have no means of optimizing the SQL statement produced by the component, so we hope the answer lies within the Oracle database.

We feel that there must be a straightforward solution to this problem hopefully avoiding any re-coding of COMM1.

PLEASE REPLY WITH SOLUTIONS TO EDDY ON:- TEL: 01222-371477
FAX: 01222-664787
EMAIL:-EDDY_at_MCTS.DEMON.CO.UK
COMTEXT:A03GG021

-- 
Edward J Hooper

Received on Fri Aug 16 1996 - 00:00:00 CEST