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: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 27 Jan 2003 14:17:41 -0000
Message-ID: <3e35444a$0$247$ed9e5944@reading.news.pipex.net>


"Omi Chandiramani" <omi_at_chandiramani.org> wrote in message news:aa6b31e0.0301241036.1995999d_at_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.

You will need top keep in mind that you are taking a deliberate decision to sacrifice performance for 'standardisation'. I'm not quite sure what you will do if you need to support 8i, where ANSI joins are not available for example. But anyway.

> 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?

I did a similar test to you (code shown below) tab1 has a number,date and varchar column for the original tests, I added an NVARCHAR2 after rereading. Results were

using a statement 30,000 inserts = 113s, delete 4s. using a prepared statement 30,000 inserts 39s,delete 4s. adding the nvarchar2 column 30,000 inserts 41s. I didn't bother batching inserts but would expect a speed increase from that as well. In other words thats over twice as fast.

You'll note that I didn't hit any Oracle bugs or use any Oracle objects. What bug am I missing? Received on Mon Jan 27 2003 - 08:17:41 CST

Original text of this message

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