execution plan is same even though it takes 10 times longer to run

20 pts.
Tags:
Execution Plan
Indexes
Performance/Tuning
I have a query that accesses a table with a non-clustered index. When I drop the index and run the query, it takes 1.25 minutes to run. With the index, it takes 2 seconds to run. Yet, the execution plan is the same with or without the index. How can that be? Thank you!
Asked: Last updated:
Related Questions
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

It shouldn’t be the same query plan. Can you post both of the execution plans? Also what database platform is this.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Abbi
    The platform is SQL Server 2005 using the SQL Query Analyzer. The table that contains the index is called within a function within the Stored Procedure.
    20 pointsBadges:
    report
  • Jerry Lees
    SQL indexing is a wonderful thing! I've saved many of thousands of dollars by talking DBA's and Web Developers into "just trying" an index when a server is running slow for no apparent reason and there is not any sign of a resource problem on the server. Mrdenny will definately be able to help you in this area if you post both execution plans and let him/us know what database platform you're using. Be sure and check out my blog here on ITKE for more Network administration and VBSCRIPT tips. The VBScript Network and Systems Administrator's Cafe
    5,335 pointsBadges:
    report
  • Ross.bradbury
    The execution plan hides the cost of calling the function. I believe functions that access data are relatively new to SQL Server and the execution plan still tends to consider the function as being something simple, like adding some numbers that were passed in. Try executing the code that is in the function for some example values that would be called during your normal procedure, and you should then be able to see a difference in that execution plan when the index is in place and when it is not. I try to avoid user defined scalar functions that access data because you can't see an accurate execution plan from statements that use them. Hopefully this helps a bit.
    40 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: