Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: JDBC / Oracle / MS SQL Server performance anomalies

Re: JDBC / Oracle / MS SQL Server performance anomalies

From: Omi Chandiramani <omi_at_chandiramani.org>
Date: 24 Jan 2003 10:36:51 -0800
Message-ID: <aa6b31e0.0301241036.1995999d@posting.google.com>


Keep in mind that this is a question about using JDBC code to write to different databases. One the the principles I'd like to stick to is that I want the exact same JDBC code to execute on either database. In other words code lke this is simply out of the question:

if (mDBType == ORACLE) {
  // do some oracle specific thing
} else if (mDBType == SQLServer) {
  // dome some sql server specific thing } else {
  // do something generic
}

The idea behind abstracting the interface to the database is to avoid such code after all.

"Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:<BT0Y9.14406$6G4.4473_at_sccrnsc02>...
> 1. Read the docs so you can write efficient code for the database part. You
> should be using a parameterized query and just rebinding and executing.

Please read my original post. I understand the increase in performance that using bind variables (implemented using a PreparedStatement in JDBC) will get you. The problem is that in the Oracle drivers there is a bug related to using PreparedStatement objects to write to NVARCHAR fields. The workaround for the bug involves calling certain functions only present in the Oracle JDBC objects - thus this is unacceptable.

*However*, I have tried using PreparedStatement in slightly different test which does not write out strings to the database. This of course increases performance to a large degree, but *still* SQL Server is twice as fast as Oracle. Why? And more importantly what can I do about it?

> Additionally, turn off auto commit.
> Those stupid JDBC drivers assume one wants autocommit - what is the point of
> having a database if you are going to autocommit? - on.

You dont sound like a Java programmer. :-)

If I run the test with auto commit off, and then commit every 10000 inserts, perfomance almost doubles for *both* databases. Again, SQL Server remains twice as fast. Why? And what can I do about it?

> What you are doing is awful and is very non-scalable. So if the objective
> is to prove that one can write inefficient code for the database interaction
> then this is along the correct path.

The point was not to present elegant/efficient JDBC code. The point was that given that the code stays the same (in all its inefficiency) why is the execution time so different? In any case, even elegant JDBC code (using autoCommit set to false, and using parameterised statements) is showing that SQL Server is twice as fast as Oracle.

Thanks for your response.

Omi

PS: Please dont take my comments to be about some form of Oracle bashing. Believe me I'd rather be going the other way - but my data simply doesn't justify that at all. Received on Fri Jan 24 2003 - 12:36:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US