title: JDBC connection to very busy SQL 2000: selectMethod=cursor vs selectMethod=direct?
body: <p>In the process of trying to help out an app dev team with performance issues on a SQL 2000 server (from a bunch of Java applications on separate app servers), I ran a SQL trace and discovered that all calls to the database are full of API Server Cursor statements (sp_cursorprepexec, sp_cursorfetch, sp_cursorclose). </p>
  
  <p>Looks like they're specifying some connection string properties that force the use of server-side cursors, retrieving only 128 rows of data at a time: (From <a href="http://msdn.microsoft.com/en-us/library/Aa172588" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/Aa172588</a>)</p>
  
  <blockquote>
    <p>When the API cursor attributes or
    properties are set to anything other
    than their defaults, the OLE DB
    provider for SQL Server and the SQL
    Server ODBC driver use API server
    cursors instead of default result
    sets. Each call to an API function
    that fetches rows generates a
    roundtrip to the server to fetch the
    rows from the API server cursor.</p>
  </blockquote>
  
  <p><strong>UPDATE</strong>: The connection string at issue is a JDBC connection string parameter, <code>selectMethod=cursor</code> (which enables the server-side cursors we discussed above) vs the alternative <code>selectMethod=direct</code>. They have been using <code>selectMethod=cursor</code> as their standard connection string from all apps.</p>
  
  <p>From my DBA perspective, that's just annoying (it clutters the trace up with useless junk), and (I would speculate) is resulting in many extra app-to-SQL server round trips, reducing overall performance.</p>
  
  <p>They apparently did test changing (just one of about 60 different app connections) to <code>selectMethod=direct</code> but experienced some issues (of which I have no details) and are concerned about the application breaking. </p>
  
  <p>So, my questions are:</p>
  
  <ul>
  <li>Can using <code>selectMethod=cursor</code> lower application performance, as I have tried to argue? (by increasing the number of round trips necessary on a SQL server that already has a very high queries/sec)</li>
  <li>Is <code>selectMethod=</code> an application-transparent setting on a JDBC connection? Could this break their app if we change it? </li>
  <li>More generally, when should you use <code>cursor</code> vs <code>direct</code>?</li>
  </ul>
  
  <p>Also <a href="https://serverfault.com/questions/177391/">cross-posted to SF</a>.</p>
  
  <p><strong>EDIT</strong>: Received actual technical details that warrant a significant edit to title, question, and tags.</p>
  
  <p><strong>EDIT</strong>: Added bounty. Also added bounty to the SF question (this question is focused on application behavior, the SF question is focused on SQL performance.) Thanks!!</p>