Many Rows Select

[OSchubert]

Hi DOA fans,

i have a problem, what a surprise ;-)

I must select a lot of Rows from our database to export into excel format file. At this time, we use the TOracleQuery. Is there a way to speed up the result of this query? Maybe via server-stored-procedure how returns a complete table, instead of serve every single row?

The answer time is very good (less than 200 ms), but the network trafic is too high, so it costs more than 2 seconds to complete the querys.

We execute up to 10 SQL's which returns nearly 5.000 up to 20.000 rows for one SQL. This runs nearly 25.000 times a day (mostly from 8:00 to 18:00). The Oracle database must serve nearly 20 GB of data a day. We use Oracle 8.1.6 and DOA 3.4.3. with NT 4.0 SP6. Oracle have 1 GB mem and there no speed or CPU problems.

Thank you for your help,
Oliver Schubert

 

[Marco Kalter]

The TOracleQuery does not fetch individual rows (unless it contains a long or long raw), but fetches a batch of records. The number of records that are transferred in one fetch is determined by the ReadBuffer property. If network roundtrips are the bottleneck, you can optimize things by increasing this property (default = 25).

You should also make sure that there are no unnecessary parse operations. If you use dedicated TOracleQuery instances for each query, and just change variable values between execution, only 1 parse will occur. This will save execution time and network roundtrips.

Another possible optimization is to combine multiple queries, which are now executed separately, into one join.

A stored procedure will only help if you don't actually need all returned rows, but some aggregated value.

After these optimizations there is not much you can do from the application side. On the server you can perhaps optimize the tables for the queries that are involved in this process. Use SQL Trace and the tkprof utility to find out where the server bottlenecks might be.

------------------
Marco Kalter
Allround Automations