(Posted first on SO, but its not getting any answers there. Cross-posting to reach a larger audience, since it is cross-discipline.)
In the process of trying to help out an app dev team with some performance issues on a SQL 2000 server, 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).
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 http://msdn.microsoft.com/en-us/library/Aa172588)
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.
UPDATE: They have multiple Java apps using a JDBC connection to the SQL server with
selectMethod=cursor specified (as opposed to
From my DBA perspective, that's just annoying (it clutters the trace up with useless junk), and in all likelihood is resulting in many extra app-to-SQL server round trips, reducing overall performance.
They've apparently tested
selectMethod=direct in a very limited way (changed just one application out of ~60) and experienced some issues of some sort (technical details of which I do not have).
So, my questions are:
- What's the performance impact of
directon an already busy SQL 2000 server? (I had speculated that increasing the number of round trips between SQL and APP servers can't do anything good. Was I wrong?)
selectMethodan application-transparent setting? Could this break their apps if we change it?
- When should they use
direct? Are there some types of apps that would benefit from one over the other?
Update: Found out the name of the driver parameter, which have warranted significant edits to title, body, and tags.
Update: Added bounty. Also added bounty to SO question (which is more focused on application behavior). Thanks!