Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: JDBC / Oracle / MS SQL Server performance anomalies
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. The JDBC drivers have much less to do with your
current test (I believe) than the relative speeds of the DBMSes as
configured, in doing what the drivers (or any other API would) tell
them to do.. Getting the DBMSes optimized to make an insert/delete
test fair would take some DBMS/platform work.
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).
Joe Weinstein at BEA
Omi Chandiramani wrote:
> 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 - 18:01:40 CST