Re: JDBC / Oracle / MS SQL Server performance anomalies

Omi Chandiramani wrote:

> Joseph Weinstein <joe_at_bea.com.remove.this> wrote in message news:<3E308261.4720EB94_at_bea.com.remove.this>...
> > If what you want to judge is *jdbc*, you should eliminate any DBMS
> > activity at all. No updates, inserts, or deletes. Just do a loop of a
> > simple select.
>
> I'm trying to judge the performance of the entire chain of components.
> If the performance is mostly bound to the performance of the database
> itself then I want to optimise that.

In order to know where you want to put your effort, you need to isolate the effects of each link in the chain. In my opinion, the place where the most time is gained or lost is in the database, not the API. The two most important parts of the database effort are application design, and database setup. An application that is poorly designed, to fetch bunches of raw data and massage and condense it at the client-end before presenting it to the user, or sending something back to the DBMS, will always be much slower than an application that 'builds their saw mill where the trees are'. Set-based processing of raw data by stored procedures in the DBMS will always beat one-row-at-a-time client-based programming. Note that this will require DBMS-specific expertise because each DBMS has it's own way of doing stored procedures.

   After you have an application that is logical in the relational programming sense, then you should apply the DBMS-specific tuning that can be done. At this point you will have extracted about 95% of the performance you're going to get. Fiddling with the client end APIs will usually get you the rest. In other words, calling a prepared statement from a client 3000 times to affect one row each time will always be slower than a plain statement executing SQL that does all 3000 rows at once.

Joe Weinstein at BEA

>
>
> > them to do.. Getting the DBMSes optimized to make an insert/delete
> > test fair would take some DBMS/platform work.
>
> What kinds of things can I do to Oracle to make the inserts run
> faster?
>
> > The commit() call in your code is superfluous because JDBC connections
> > come in autoCommit(true) mode, so there's nothing to commit unless
> > you first set autoCommit(false).
>
> You are right. That code was in there from when auto commit was set to
> false and I was doing the commits manually. I've removed it from my
> code.
>
> Omi
Received on Fri Jan 24 2003 - 08:18:47 CST