title: Overhead with Microsoft JDBC driver when executing a stored procedure
body: <p>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. </p>
  
  <pre><code>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();
  }
  </code></pre>
  
  <p>Using SQL Profiler I see that the JDBC driver generates the following sql statements to make the call - </p>
  
  <pre><code>declare @P1 int
  set @P1=1
  exec sp_prepexec @P1 output, N'@P0 int', N'EXEC getEmployeeManagers @P0', 50
  select @P1
  </code></pre>
  
  <p>So this means when I execute a stored procedure using a CallableStatement, the <code>sp_prepexec</code> 
  statement is called. And later when I close the statement, the <code>sp_unprepare</code> 
  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 - </p>
  
  <pre><code>exec getEmployeeManagers @P0=50
  </code></pre>