SQL stored procedure is taking too much time to execute

Tags:
SQL
Stored Procedures
One store procedure is working fine at one location. But the same store procedure is taking too much time to execute in different location on same network. What would be reason for this and how to reduce response time?
1

Answer Wiki

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

exec [dbo].[IBS_fetchreleasedinpodiumgridnew] 1 with recompile

Discuss This Question: 10  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.
  • ToddN2000
    Is this running over the same local database tables or do they have have their own? We have some procedures that run that access DB2 tables. The stored procedure used is the same but one accesses a table with about 100K rows the other table (same name different location) has about 3 million rows.
    136,240 pointsBadges:
    report
  • mukeshkumar12
    Database is on network. we are accessing this from two different location. 
    30 pointsBadges:
    report
  • TheRealRaven
    Could be different hardware, different fixes, different activities in the system, different logging, different history statistics, different data volumes, different indexes, etc. No way for us to know. Far too many possibilities.
    36,880 pointsBadges:
    report
  • TheRealRaven
    @mukeshkumar12: Your last comment posted before I had a chance to see it. Since there is only a single location where the SP runs, that makes your question very different from how it originally reads. (It's almost the opposite.) In this case, a whole bunch of other factors need to be considered and more details are needed for clarification.

    The most important missing detail is whether or not the stored proc actually runs longer/shorter or it's only that the response times are different between the two access locations. Run-time and response-time are very different things. It's not at all clear what you are asking about.

    Also, does the same user ID make the requests? And do the two access locations run under different work management descriptions?
    36,880 pointsBadges:
    report
  • TheRealRaven
    Wish I could go back to see the original of that last comment of mine. Sorry it showed up looking so bizarre.
    36,880 pointsBadges:
    report
  • TheRealRaven
    Ah... looks like some formatting was done so that the comment now reads more clearly. Thanks to whomever...
    36,880 pointsBadges:
    report
  • edwinjhoffer
    It seems like you have created bad indexes.
    3,740 pointsBadges:
    report
  • AjitK29
    Try clearing the execution plan cache.
    2,545 pointsBadges:
    report
  • TheRealRaven
    It's unlikely that indexes are related since two locations use the same SP... unless the locations reference two separate sets of data. I.e., one location might cause the SP to SELECT ... WHERE LOCATION = 'AA' and the other has WHERE LOCATION = 'BB' or some similar selection difference.

    If that's the case, then the question again becomes very different. But indexes still wouldn't seem relevant, nor would the execution plan cache.

    Too many unanswered questions have collected. No way to guess what the problem is nor even to tell if it's perfectly normal.
    36,880 pointsBadges:
    report
  • carlosdl
    I agree, this has probably nothing to do with indexes or execution plans.

    A deep investigation would be needed to determine the root of the problem, but without participation from the OP, that is just not possible.
    85,865 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: