Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: JDBC / Oracle / MS SQL Server performance anomalies
I think Mr. Townsend makes some excellent good points. Let me add a few
more.
1. Oracle beats the pants off SQLServer with both running on any non-MS
windows platform. (same non-MS Windows platform) Unfair your say? Why?
You are reducing the "comparison" to the lowest common denominator.
2. If the objective is to test speed of a particular statement (its a rather
lame test - no test of multi user scalability, concurrency etc. things that
databases are for.) then one should put each in the best light. (that is
write the code that is most efficient for the db given the business needs)
This type of test is like the MIPS tests that they used to do on CPUs
decades ago. (MIPS stands for meaningless instructions per second) Thus
things like concurrency and transaction boundries and scalability of the db
are rather important.
3. Use the thick client oci java driver. It is more efficient. Also use
the array interface with bind variables. You could submit 100 inserts at a
time. On Windows you are probably naturally using a thick driver. Try the
same test from a unix machine for both. Might be a difference.
4. Question like why is X so slow is in the category of the question "Have
you stopped beating your wife yet?" No information or analysis of what is
going on in the db etc. Run stats pack and give us the report from it.
Then maybe we could tell you. But lack of much information makes it seem
more like a troll.
Jim
-- Replace part of the email address: kennedy-down_with_spammers_at_attbi.com with family. Remove the negative part, keep the minus sign. You can figure it out. "Omi Chandiramani" <omi_at_chandiramani.org> wrote in message news:aa6b31e0.0301241036.1995999d_at_posting.google.com...Received on Fri Jan 24 2003 - 20:32:11 CST
> 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.