Re: Deadly sins againts database performance/scalability

Dusan Bolek wrote:

> Hello,
> I'm just creating some document how developers should/must not behave
> when dealing with Oracle database. I written down some basic rules and
> came with idea what's the most stupid thing that you can do to your
> database.
> I have two on mind, first one is not using BIND variables and the
> second one is using COMMIT in a loop statement. However, there will be
> more of these.
> So I have question to you here. What's the most stupid, but in real
> world foundable, mistakes in development applications using Oracle
> datatabase?

The number one performance rule for Oracle or any other RDBMS is "Don't Move the Data". The biggest sin is treating the DBMS as a dumb filing system, and pulling huge wads of raw data out into a client to massage and return row-by-row or digest into a report. DBMSes are designed for efficient set-based processing. Try to extract only the data needed to display. For any data extracted that is non-volatile or under the total control of the one client application, to cache retrieved data in a long-running client (or middle tier) to avoid burdening the DBMS with repeated identical queries.

    Do all the heavy big-volume processing where the data is, in the DBMS with procedures. You build your sawmill where the trees are.

    I know of one big application vendor whose payroll program is a straight port of an ISAM app to RDBMS. It takes 8 hours on big iron to 'balance the checkbook' of 40,000 employees. If this were done in procedures, it'd take minutes. I know of another big app vendor whose client interface has about 250 separate business methods. A typical user would do his/her work calling these methods about 50-100 times a day. More than 80% of these methods have processing that includes a fresh query to the DBMS to return a list of all the countries in Europe. I understand the political volatility in Europe has been higher recently... ;-)

Joe Weinstein at BEA

>
> --
> with regards
>
> Dusan Bolek
Received on Tue Nov 25 2003 - 11:30:47 CST