Re: JDBC / Oracle / MS SQL Server performance anomalies

Hi,

It is known that Oracle is not "fastest" database, but "fast and reliable". According to tpc benchmark results MSSQL is ahead of Oracle in low size tests. Also driver can be not optimized for speed but for readability.

To get more realistic test I would reuse Statement object and put commit() before timer stop.

AlexV.

"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 Fri Jan 24 2003 - 07:30:32 CST