Overhead with Microsoft JDBC driver when executing a stored procedure

Question

I am using Microsoft JDBC Driver 2.0 with SQL Server 2005. To explain my question better, let me start with a sample code to call a stored procedure.

public static void executeSproc(Connection con) 
{
  CallableStatement cstmt = con.prepareCall("{call dbo.getEmployeeManagers(?)}");
  cstmt.setInt(1, 50);
  ResultSet rs = cstmt.executeQuery();

  while (rs.next()) {
     // print results in the result set
  }
  rs.close();
  cstmt.close();
}

Using SQL Profiler I see that the JDBC driver generates the following sql statements to make the call -

declare @P1 int
set @P1=1
exec sp_prepexec @P1 output, N'@P0 int', N'EXEC getEmployeeManagers @P0', 50
select @P1

So this means when I execute a stored procedure using a CallableStatement, the sp_prepexec statement is called. And later when I close the statement, the sp_unprepare is called. This seems to be the default behavior of the JDBC driver. The problem is, the overhead to generate a prepared statement and then close it has performance impact. Is there a way for the driver to execute the stored procedure directly? Why can't the driver just do this -

exec getEmployeeManagers @P0=50

Answer 1

Try using the jTDS driver for SQLServer. I use it at work and it seems to be a lot better than the driver provided by MS.