jdbc performance issuses with 2005 vs 2000

I cannot get performance out of sql server 2005 through jdbc connections.
I have used multiple drivers against 2000 and 2005. 2000 always comes out on top.
I installed the os the same, configured the raid array the same, configured the os the same, configured the database the same, installed the software the same, etc.

It seems to come down to the jdbc driver and the way the database handles batch requests from jdbc. Is there some configuration in 2005 that I need to alter to improve the performance of batched inserts through the jdbc driver?

I am sorry to hear that the 2005 driver isn't delivering the same performance as the 2000 driver for you here. Do you have a small code sample that you could share that demonstrates where you are seeing the decreased performance? It would be helpful to know whether the inserts are through Statement or PreparedStatement objects, what types are involved, and the driver version (v1.0, v1.1 CTP?). Also, what are you measuring (e.g. CPU utilization, client response time, inserts/second, etc.)?

Thank you,

--David Olix

JDBC Development

|||Here is the trace from the profiler.

I tried setting the following options on the url connection but it doesn't seem to make that much of a difference in increased performance.
selectMethod=cursor;sendStringParametersAsUnicode=False

I am using the 1.1 version of the jdbc driver.

Measuring inserts per second.

declare @.p2 int
set @.p2=0
declare @.p5 int
set @.p5=0
exec sp_cursorexecute 91,@.p2 output,16,8193,@.p5 output,584964,1,140830000,113364,19000101,99991231,'JERSEY','JERSEY',3,2,2
select @.p2, @.p5|||

For better performance, you generally should not use selectMethod=cursor. Setting selectMethod=cursor forces every forward only/read only query to request a server cursor. For queries that return only a few rows, the overhead of creating and destroying a server cursor can be relatively high. Server cursors (and selectMethod=cursor) typically yield better performance for queries that return a large number of rows.

Note that you should not need to use server cursors at all with any INSERT or other DML statement, because these kinds of statements do not produce a result set. Are you using the executeUpdate() method or the execute() method to execute the INSERT statements? By using executeUpdate(), you let the driver know that the SQL is expected to produce an update count, rather than a result set, so it won't request a server cursor, even when selectMethod=cursor is set. Also, you may want to consider doing the inserts in a batch with autocommit set to false.

Thank you,

--David Olix

JDBC Development