Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2003
    Posts
    17

    Unanswered: Indexes with Server Cursor

    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?

  2. #2
    Join Date
    Jul 2002
    Location
    IA
    Posts
    28
    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.
    Thanks,
    Jason

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    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.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  4. #4
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    You may want to reconsider using a cursor -- there may be better ways to obtain the data you are seeking.
    Thanks,

    Matt

  5. #5
    Join Date
    Apr 2003
    Posts
    17
    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.

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Try using QUERY Execution plan in QA using the query.
    BTW what is the SQL version & SP level and how do you connect from application to database?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •