SQL Server / ODBC scrollable result set performance

I've got an application connecting to a SQL Server database via ODBC, and providing query functionality through a Web interface. In order to map the datab开发者_StackOverflow中文版ase onto our web framework, I am using a scrollable result set. Most of the rest of the application uses a forward-only cursor.

I've got an order table with a huge number of rows, and a query:

SELECT * FROM order

If I run this with my own command line program, it immediately starts returning data. If I only fetch 25 rows, the query runs very quickly. If I run it on the web screens, the query times out after 30 seconds, without returning any data.

Both methods go through the same sequence of calls, except for the cursor type. In the command line program, I use SQLSetStmtAttr to set SQL_ATTR_CURSOR_TYPE to SQL_CURSOR_FORWARD_ONLY, while in the web version, I use SQL_CURSOR_DYNAMIC.

I've gone in with the debugger to find out where the delay is, and what I've seen is the SQLExecute call takes around 50 seconds with the cursor set to dynamic. For info, I was using SQL_CURSOR_STATIC before, and SQL_CURSOR_DYNAMIC is my first attempt to fix the problem.

Is there any way to get a performant scrollable result set in SQL Server via ODBC?

Scrollable cursors are seriously a no no no on sql server regardless what API you use - and ODBC makes them ahrder. Get around them pretty much at any cost.