JDBC / Oracle / MS SQL Server performance anomalies

From: Omi Chandiramani <omi_at_chandiramani.org>
Date: 23 Jan 2003 15:27:39 -0800
Message-ID: <aa6b31e0.0301231527.4853dc07@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 - 17:27:39 CST