Optimizing performance

HI!

We are currently evaluating MS JDBC against jTDS and i-net Una regarding performance.

Our application does only primitive JDBC stuff:
- single queries
- cursor queries on larger resultsets
- update/insert/delete statements

So, no prepared statements, updatable statements, etc.

To test this, we have written a very simple benchmark. This is no real application of course, but it shows the direction. We have verified this already in the real world with Una and jTDS. The results are roughly the same (in percentage) in the real application compared with the benchmark.

Now, MS JDBC is also in the game.

When using cursors, the performance is the same as jTDS, but slower than Una. However, the performance tuning tips from the MS web site do not help. For example, a updatable cursor is faster than a read only cursor. The same is true for jTDS BTW, but not for Una.
When I try to use a read only fast forward cursor with MS JDBC, it uses strange cursor options (16, 8193) according to the profiler and this results in worse performance...

When using single selects, MS JDBC is much slower than jTDS and jTDS is slower than Una.

So, after this long introduction, our question: Can we improve the performance of MS JDBC for our benchmark (and application)?

BTW, we also tried the new adaptive buffering but this resulted in absolutely bad performance compared to cursors and is also not usable in the real world, because we have typically multiple statements open.

I am attaching the benchmark. It uses the AdventureWorks database and is running against SQL 2005. You have to set database server and password before running.

These are the results on my desktop PC (each test is being run twice for warm up):

Running Una CursorTest...
Time: 4109
Running Una CursorTest...
Time: 3953
Running jTDS CursorTest...
Time: 5125
Running jTDS CursorTest...
Time: 4891
Running MS CursorTest...
Time: 5234
Running MS CursorTest...
Time: 4891
Running Una SingleSelectTest...
Time: 3406
Running Una SingleSelectTest...
Time: 3375
Running jTDS SingleSelectTest...
Time: 3828
Running jTDS SingleSelectTest...
Time: 3766
Running MS SingleSelectTest...
Time: 6047
Running MS SingleSelectTest...
Time: 6031


And hints would be greatly appreciated.

Thomas


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.Date;

public class JdbcBench {
private static final int MAX_CURSOR_RUNS = 1000;
private static final int MAX_SINGLESELECT_RUNS = 10000;
private static final int NUMBER_OF_LOOPS = 2;
private final static String SERVER = "kapc387";
private final static String DATABASE = "AdventureWorks";
private final static String TABLE = "Person.Contact";
private final static String LOGIN = "sa";
private final static String PASSWORD = "p2plus";
private final static int QUERY_TIMEOUT = 30;
private final static String UNA_CLASS_NAME = "com.inet.tds.TdsDriver";
private final static String JTDS_CLASS_NAME = "net.sourceforge.jtds.jdbc.Driver";
private final static String MS_CLASS_NAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

public static void main(String[] args) throws Exception {
Class.forName(UNA_CLASS_NAME).newInstance();
Class.forName(JTDS_CLASS_NAME).newInstance();
Class.forName(MS_CLASS_NAME).newInstance();

cursorTest();
// singleSelectTest();
}

private final static void cursorTest() throws Exception {
Connection conn;
Statement stmt;
ResultSet rs;
int columnCount;
ResultSetMetaData meta;
Date d1, d2;
String sql = "select * from " + TABLE;

for(int t = 0; t < NUMBER_OF_LOOPS; t++) {
System.out.print("Running Una CursorTest...");
conn = getUnaConnection();

d1 = new Date();
for(int i = 0; i < MAX_CURSOR_RUNS; i++) {
stmt = getUnaStatement(conn, true);
rs = stmt.executeQuery(sql);
meta = rs.getMetaData();
columnCount = meta.getColumnCount();
rs.next();
rs.close();
stmt.close();
conn.commit();
}
d2 = new Date();
System.out.println(" Time: " + (d2.getTime() - d1.getTime()));
conn.close();
}

for(int t = 0; t < NUMBER_OF_LOOPS; t++) {
System.out.print("Running jTDS CursorTest...");
conn = getJtdsConnection();

d1 = new Date();
for(int i = 0; i < MAX_CURSOR_RUNS; i++) {
stmt = getJtdsStatement(conn, true);
rs = stmt.executeQuery(sql);
meta = rs.getMetaData();
columnCount = meta.getColumnCount();
rs.next();
rs.close();
stmt.close();
conn.commit();
}
d2 = new Date();
System.out.println(" Time: " + (d2.getTime() - d1.getTime()));
conn.close();
}

for(int t = 0; t < NUMBER_OF_LOOPS; t++) {
System.out.print("Running MS CursorTest...");
conn = getMsConnection();

d1 = new Date();
for(int i = 0; i < MAX_CURSOR_RUNS; i++) {
stmt = getMsStatement(conn, true);
rs = stmt.executeQuery(sql);
meta = rs.getMetaData();
columnCount = meta.getColumnCount();
rs.next();
rs.close();
stmt.close();
conn.commit();
}
d2 = new Date();
System.out.println(" Time: " + (d2.getTime() - d1.getTime()));
conn.close();
}
}

private final static void singleSelectTest() throws Exception {
Connection conn;
Statement stmt;
ResultSet rs;
int columnCount;
ResultSetMetaData meta;
Date d1, d2;
String sql = "select * from " + TABLE + " where ContactID = 1160";

for(int t = 0; t < NUMBER_OF_LOOPS; t++) {
System.out.print("Running Una SingleSelectTest...");
conn = getUnaConnection();

d1 = new Date();
for(int i = 0; i < MAX_SINGLESELECT_RUNS; i++) {
stmt = getUnaStatement(conn, false);
rs = stmt.executeQuery(sql);
meta = rs.getMetaData();
columnCount = meta.getColumnCount();
rs.next();
rs.close();
stmt.close();
conn.commit();
}
d2 = new Date();
System.out.println(" Time: " + (d2.getTime() - d1.getTime()));
conn.close();
}

for(int t = 0; t < NUMBER_OF_LOOPS; t++) {
System.out.print("Running jTDS SingleSelectTest...");
conn = getJtdsConnection();

d1 = new Date();
for(int i = 0; i < MAX_SINGLESELECT_RUNS; i++) {
stmt = getJtdsStatement(conn, false);
rs = stmt.executeQuery(sql);
meta = rs.getMetaData();
columnCount = meta.getColumnCount();
rs.next();
rs.close();
stmt.close();
conn.commit();
}
d2 = new Date();
System.out.println(" Time: " + (d2.getTime() - d1.getTime()));
conn.close();
}

for(int t = 0; t < NUMBER_OF_LOOPS; t++) {
System.out.print("Running MS SingleSelectTest...");
conn = getMsConnection();

d1 = new Date();
for(int i = 0; i < MAX_SINGLESELECT_RUNS; i++) {
stmt = getMsStatement(conn, false);
rs = stmt.executeQuery(sql);
meta = rs.getMetaData();
columnCount = meta.getColumnCount();
rs.next();
rs.close();
stmt.close();
conn.commit();
}
d2 = new Date();
System.out.println(" Time: " + (d2.getTime() - d1.getTime()));
conn.close();
}
}

private static Connection getUnaConnection() throws Exception {
Connection connection;
String url = "jdbc:inetdae7:" + SERVER + "?database=" + DATABASE + "&user=" + LOGIN + "&charset=Cp1252" + "&password=" + PASSWORD;

DriverManager.setLoginTimeout(10);
connection = DriverManager.getConnection(url);
connection.setAutoCommit(false);
return(connection);
}

private static Statement getUnaStatement(Connection conn, boolean useCursor) throws Exception {
Statement stmt;

stmt = conn.createStatement();

stmt.setQueryTimeout(QUERY_TIMEOUT);

if(useCursor) {
stmt.setCursorName("C" + java.lang.Math.abs(stmt.hashCode()));
}
return(stmt);
}

private static Connection getJtdsConnection() throws Exception {
Connection connection;
// String url = "jdbc:jtds:sqlserver://" + SERVER + "/" + DATABASE + ";user=" + LOGIN + ";password=" + PASSWORD + ";socketTimeout=" + QUERY_TIMEOUT + ";sendStringParametersAsUnicode=false";
String url = "jdbc:jtds:sqlserver://" + SERVER + "/" + DATABASE + ";user=" + LOGIN + ";password=" + PASSWORD + ";socketTimeout=" + QUERY_TIMEOUT;

DriverManager.setLoginTimeout(10);
connection = DriverManager.getConnection(url);
connection.setAutoCommit(false);
return(connection);
}

private static Statement getJtdsStatement(Connection conn, boolean useCursor) throws Exception {
Statement stmt;

if(useCursor) {
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
stmt.setFetchSize(128);

} else {
stmt = conn.createStatement();
}
stmt.setQueryTimeout(QUERY_TIMEOUT);
return(stmt);
}

private static Connection getMsConnection() throws Exception {
Connection connection;
String url = "jdbc:sqlserver://" + SERVER + ";database=" + DATABASE + ";user=" + LOGIN + ";password=" + PASSWORD + ";lockTimeout=" + QUERY_TIMEOUT;

DriverManager.setLoginTimeout(10);
connection = DriverManager.getConnection(url);
connection.setAutoCommit(false);
return(connection);
}

private static Statement getMsStatement(Connection conn, boolean useCursor) throws Exception {
Statement stmt;

if(useCursor) {
// stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
// stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
stmt = conn.createStatement(com.microsoft.sqlserver.jdbc.SQLServerResultSet.TYPE_SS_SERVER_CURSOR_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(128);

} else {
stmt = conn.createStatement();
}
stmt.setQueryTimeout(QUERY_TIMEOUT);
return(stmt);
}

}

Relevant Pages

  • Re: JDBC and ResultSet problem
    ... > Is it not possible to change default read cursors to be server side ... > to switch back to the jTDS driver. ... connection to retrieve a ResultSet and do something else at the same ...
    (comp.lang.java.databases)
  • Re: Trusted connections
    ... > with jTDS you will have to provide the Windows username and password, ... Ok so please explain how you are claiming that the connection is ... Maybe I dont understand.. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Web application using jdbc driver an OEM application?
    ... jTDS is thread safe at the Connection level. ... each using a separate Statement created from that Connection. ... it's completely safe to use jTDS within a web ...
    (comp.lang.java.databases)
  • Re: Trouble connecting with JTDS
    ... Unable o add connection, ... I'm wondering if the JTDS is even installed correctly... ... > getting a successful connection to an existing SQLEXPRESS database I ... I have installed the JTDS connector to access the SQLEXPRESS ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Trouble connecting with JTDS
    ... Unable o add connection, ... I'm wondering if the JTDS is even installed correctly... ... > getting a successful connection to an existing SQLEXPRESS database I ... I have installed the JTDS connector to access the SQLEXPRESS ...
    (comp.lang.java.databases)
Loading