Re: Performance: "pl/sql" vs. "OS b-tree files"

"Ignacio" <barbudo_at_ono.com> wrote in message news:a4U9c.1040$9c.130331_at_news.ono.com...
> Hi,
>
> we're developing an application in which performance is a very important
> issue (what a new!:)
> Database is Oracle 9.2, application language is Java 1.4.2., OS is Solaris
8
> (RAM 8GB, 4x750Mhz CPU).
>
> The process needs to query for every object several times (around 10) the
> same configuration tables, which are stored in Oracle. Every process has
> millions of objects, so the number of consults made to Oracle would be
> rather high.
> No INSERT statements would be made, only SELECTs.
>
> The two alternatives are:
>
> * wrap the SQL statements in pl/sql packages to minimize the number of
> SELECT statements made to Oracle. The packages would be called using JDBC.
> The config tables would be set as CACHED, and perhaps index-organized.
>
> * dump all the configuration tables in a OS file and, using Java classes
> based on B-Trees, build a B-Tree file and make all the searches using Java
> methods against this B-tree file. This file would be cached in memory by
the
> OS.
>
> It is supposed that the Java methods querying in-memory data would beat
the
> pl/sql procedures querying the Oracle tables... Does it worth to dump all
> the information from Oracle to a B-Tree file, having in mind that Oracle
> uses the same technique to build indexes? Which solution would be more
> efficient?
>
> Thank you very much for your comments. Any hint would be welcomed.
>
>

I think it is a bad idea to write a database engine yourself. This is, in effect, what you are proposing to do with your second option.

Why are the configuration tables in the database in the first place? There must be some reason to store them in the database. You just do not pay a lot of money to Oracle Corp. just to store data inside an Oracle database.

I am suspicious when anyone says that their data is read-only. Are you certain that this will be the only application to ever use these configuration tables? Business requirements change as market opportunities open up. If there is a future requirement to have your configuration tables updated or the structure of the configuration table changes, then you are up for a huge maintenance effort.

And speaking of maintenance, when your development team moves onto greener pastures, who are going to call to tune your 'database engine'? Certainly not c.d.o.s..

Douglas Hawthorne Received on Mon Mar 29 2004 - 06:24:04 CST