INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How do I force a stored procedure to use an index?

How do I force a stored procedure to use an index?

(OP)
I have an indexed table of 1,750,000 rows which gives excellent performance when doing a database query on a column which has a secondary non-unique index. When I try to perform a similar query in a stored procedure via a cursor however, it does a full table scan on every query. Is there a way that I can force it to use the index?  

RE: How do I force a stored procedure to use an index?

Hi,

You can try and see if the UPDATE STATISTICS statement makes any improvement. Then the stored procedure know about the current amount of data, new indexes etc, and possible optimizations will be done.

HTH,
Jarl

RE: How do I force a stored procedure to use an index?

(OP)
Thanks Jarl. That did indeed help. It most definitely uses the index on simple conditionals in the stored procedure now. Now we just need to ensure that we don't write code that switches it off! smile

Regards,

Tom.

PS Is this the only MIMER forum on the web (it seems quiet) or are there others?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close