Post reply

Slow run time

  • November 7, 2018 at 8:32 am

    #409296

    I have a query which is running slow for approx 15 mins, however, the slow run time has no impact to anyone. Creating the index improves query run time. Do you think creating index is best option or run against server resources since there is no impact, what would be the impact from server resources perspective?

    Thanks

  • November 7, 2018 at 9:05 am

    #2012706

    Does the index have a significant improvement? If it does, what reasons do you have to not add it? Why do you beleive that the report will never have any contention? Is it going to be the only query running on Server when it is run (the instance is otherwise completely idle)?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • November 7, 2018 at 9:26 am

    #2012708

    Yes it has significant improvement after creating the index. These are replicated tables and tables get populated through replication all the time.I am sure adding one index would not cause any delay on replication. However, this query runs once a week and it does not have any impact on users other than server resources, i was debating with myself creating index Vs server resources.

  • November 7, 2018 at 9:33 am

    #2012709

    It seems like you're looking for a reason, rather than having one. Based on the little detail we have, it seems there's little reason why you wouldn't add the index.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • November 7, 2018 at 9:49 am

    #2012714

    Admingod - Wednesday, November 7, 2018 8:32 AM

    I have a query which is running slow for approx 15 mins, however, the slow run time has no impact to anyone. Creating the index improves query run time. Do you think creating index is best option or run against server resources since there is no impact, what would be the impact from server resources perspective?

    Thanks

    Can you post the execution plan (an "actual" rather than "estimated" plan) as a .sqlplan file? Cheers.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • November 7, 2018 at 10:25 am

    #2012719

    Thanks! However, i don't need help on the execution plan, all i am looking for is adding index benefit vs resources.

  • November 7, 2018 at 10:51 am

    #2012724

    Adding the index allows the queries that can use it to run faster. The downside is that disk space is being used for the index. In addition, the writes to the table that affect this index, cause inserts/updates/deletes. You'd have to examine reads v writes on the table, and weigh whether the load of writes is very high v reads.

    There isn't a great way to estimate this automatically, without knowing the frequency of queries v writes for your workload.

  • November 22, 2018 at 1:27 pm

    #2014348

    Admingod - Wednesday, November 7, 2018 8:32 AM

    I have a query which is running slow for approx 15 mins, however, the slow run time has no impact to anyone. Creating the index improves query run time. Do you think creating index is best option or run against server resources since there is no impact, what would be the impact from server resources perspective?

    Thanks

    Ok, so adding index improves performance and in your case it does not matter if you do not add the index as well. I
    f that is the case then do NOT add index. There would be maintenance cost involved when an index is added(disk space, re-build index from time to time etc). If users are happy with the response time, then I would say all is well.

  • November 22, 2018 at 2:13 pm

    #2014357

    Admingod - Wednesday, November 7, 2018 10:25 AM

    Thanks! However, i don't need help on the execution plan, all i am looking for is adding index benefit vs resources.

    You don't know that.  It IS possible that the way it was written is wrong and you might not need the index to make it run fast.

    Also, my recommendation is that if you thought well enough to declare it as having poor enough  performance to actually try an index, then stop dismissing it because when you least expect it, the problem will get worse.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply