SQL Server index for cursors

Question

I have an application that sometimes runs slow and I think it is related to database cursors.

Don't have any access to the application source so I can't control the database calls but the database is open so I can add index where needed.

Problem is that I don't know really know how to speed up cursors with index.

The cursor queries are simple and look like

SELECT * FROM table WHERE field1=1 AND field2=2 ORDER BY field3, field4

(Table contains about a 1M rows. Sometimes there is a single left join as well)

If I run the query directly in SSMS it takes less than a second but when it is run from the application in a cursor it can take 30 seconds to fetch the first row (verified with sql-trace).

The fields in WHERE and ORDER BY clauses are all indexed separately.

I guess a combined index on field1,field2,field3,field4 would make it faster. Is there a way to speed it up without creating an index for each combination and order of fields?

(Just to repeat: I have no influence on how the application accesses the database. Performance can only be tuned via index)

Answer 1

One thing I always do (if possible) I run the DB Tuning Advisor.

Don't get me wrong - I don't follow all his rules and suggestions, but it is an easy way to see what's going on, how often what occures and so on. Some hours of (typical!!!) workload are good to get some basic "feeling" what's going on.

And after it you can decide to implement some of the suggestions or not. And even if you did your best in design - such a check looks what's really going on (not always predictable) and maybe you forget some statistics or a different index could help...

Answer 2

I'd change the query to use actual column names and not SELECT *, and then create a covering index on field1=1 and field2=2. If possible, I'd put a clustered index on field3 and field4.

If you're on SQL 2005+, try looking at CTE instead of cursor, or refactor your query to use temp tables.