- 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.
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. 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.
- SQL Server!= Oracle. They are very different beasts. If you are trying
to figure out what has more capability to do insert then frankly that's a
rather limited POV. In the end, if code is written correctly for the
particular database the speed of the hardware is going to be more telling in
these simplified tests.
- If you want some idea of what bind variables are and how to use them
correctly (and are unsure of what they are in the docs) go to
asktom.oracle.com and search for bind variable.
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.0301231527.4853dc07_at_posting.google.com...
> Hello all,
>
> In a nutshell, I am trying to compare performance between Oracle 9i,
> and MS SQLServer 2000, when programming in Java using JDBC. I'm
> noticing that Oracle is twice as slow as SQLServer, and am wondering
> if this large difference is a result of some mis configuration on my
> part.
>
> Here are the details:
>
> JDK 1.4.1_01
>
> Windows 2000 SP3
> Dell Latitude, 512 MB RAM, Pentium 4 1.8 GHz, 30 GB Hard drive with 20
> GB free.
>
> The databases are both installed on the above machine:
>
> Oracle 9i
> UTF-8 installation
> Enterprise Install / General Purpose db
> JDBC: Oracle 9i JDBC drivers from Oracle, Type 4
>
> MS SQL Server 2000 Personal Edition
> JDBC: i-net's Opta2000 SQL Server drivers, Type 4
>
> Both databases were installed accepting most of the default settings
> except as noted above.
>
> The crux of the code:
>
> Connection con = DriverManager.getConnection(url,
> user,
> password);
>
> long lStart = System.currentTimeMillis();
>
> String lSQL = "";
> for (int i = 1; i <= 30000; i++) {
>
> Statement lStmt = con.createStatement ();
>
> lSQL = "insert into User values (" + i +
> ", 0, N'radius://db-omi', N'user" + i +
> "', N'user" + i + "', 1)";
>
> lStmt.executeUpdate(lSQL);
> lStmt.close();
> }
>
> System.out.println ("30000 inserts took: " +
> ((System.currentTimeMillis() - lStart)/1000) + "s"
> );
>
> con.commit();
>
> lStart = System.currentTimeMillis();
>
> lSQL = "delete from User";
> Statement lStmt = con.createStatement ();
> lStmt.executeUpdate(lSQL);
> lStmt.close();
>
> System.out.println ("Delete of 30000 rows took: " +
> ((System.currentTimeMillis() - lStart)/1000) + "s"
> );
>
> con.commit();
> con.close();
>
>
> Very simple: 30000 inserts and then a delete of all the rows just
> inserted. I made sure that the User table was empty before the test
> began.
>
> The output of the above code against Oracle:
>
> 30000 inserts took: 105s
> Delete of 30000 rows took: 4s
>
> and against SQLServer:
>
> 30000 inserts took: 45s
> Delete of 30000 rows took: 2s
>
>
> Known issues:
>
> - I could optimise this much further by turning off auto commit on the
> connection, and then manually commiting once in while.
> - A PreparedStatement could speed things up a lot! PreparedStatements
> cannot be used because of an issue with the Oracle JDBC drivers when
> writing to NVARCHAR fields.
> - The machine I'm running on is *not* a server machine.
>
> I understand that none of the above issues are ideal. However, I still
> want to understand why the Oracle test takes almost twice as long than
> the SQLServer test, all other factors (desirable or not) being equal.
>
> So, the basic questions I have are this:
>
> Is this difference in performance expected / normal?
> If not, what kinds of tuning of the database / my environment / the
> code should I be doing to optimise the performance of Oracle to be
> comparable to SQL Server?
> (As far as the code is concerned I would ideally avoid code that did
> something different based on which database was being used.)
>
> Thanks,
> Omi
Received on Thu Jan 23 2003 - 19:44:01 CST
