I am working with an application that uses a server cursor on SQL 2000. Whenever the program tries to search for a record, the search takes forever. I was hoping I could just search for the record within the entire dataset without doing a sql select command. It almost seems like the server cursor isn't using the indexes I have setup. Does anyone know if the server cursor will take advantage of the indexes?
You may need to recompile the stored proc if you created the index after the fact. Or if the index has been around for a while you may need to run a DBCC REINDEX. If the index is out of date or bad SQL Server will automatically quit using it.
Make sure you are not overriding SQL with a HINT.
If this doesn't help can you post the CREATE TABLE and a sample of the SELECT. Maybe you don't have the WHERE clause covered by the INDEX and SQL thinks it can do better without it.
As referred make sure to run DBCC checks to fine tune the performance. Also runn PROFILER during this operation and see where it lacks, submit the trace to INDEX TUNING WIZARD for any recommendation on indexes that may help.
Unfortuntely I don't know of any other way, the main obstacle with this program is that I need to have all the data there for the user to see. Currently I do have the searches returning subsets with a sql statement, but I would very much like to be able to use a locate command instead.
I tried reindexing but that didn't work either. Thanks for all your replies, any other suggestions? I'm stumped
Originally posted by MattR
You may want to reconsider using a cursor -- there may be better ways to obtain the data you are seeking.