Re: Delphi - Oracle performance

On Sun, 30 Mar 97 15:34:10 GMT,
Joost Schoonderbeek <joosts_at_worldaccess.nl> wrote:

>I've writted a database application in Delphi on a Oracle database with about
>200.000 surnames. No fancy relations, just a simple (large?) database.
>Accessing the database with a TDBGrid, TDataSource and TTable/TQuery is not
>quick enough. While standing on record 150.000 three times PageUp hangs the
>computer for almost 1 minute.
>Yes, I've created an index on the right fields. It seems that Oracle can't use
>the Index while scrolling backwards ???
>Does anybody know wheather this is the problem or am I asking the impossible.

The index does not have anything to do with scrolling. Indexes are used only when selecting a set of records from tables.

When you open your TQuery, that's when the index is used. Oracle then returns the selected rows and you see them displayed on your grid. When you scroll, you are actually scrolling what you already have returned with your TQuery.

Oracle is set-oriented as opposed to record-oriented (like Paradox or xBase). Oracle does not know how to scroll. Oracles knows how to fetch selected records one at a time only in a forward direction. When you scroll your Delphi grid, that's Delphi doing the work for you, not Oracle. Delphi uses a cache in the client to facilitate scrolling backward. Others may have a varying opinion, but selecting 200000 records and scrolling through them on a grid is not a good practice.

Increasing the schema cache size in the BDE may speed up scrolling. Allowable size is 0 to 32. Default is 8.

HTH,
Lito

Lito Dizon
adizon_at_us.net Received on Mon Mar 31 1997 - 00:00:00 CST