9/27/2016

How to resolve this Query Optimization issue?

Query Optimization Issue:

Assume that a query is executing by a application developer or a client. The query has all the indexes on the columns which are required and performed well upto certain time, suddenly got complaints about the same query that it is not performing well taking too much time to retrieve. Please leave your valuable comments how you people can resolve this issue.

5 comments:

  1. Personally, I've seen occurrences of indexes being accidentally deleted. So, I would ensure all the expected indexes still exist.

    Then, i would run the query through the Visual Explain tool to see if it found any glaring bottlenecks.

    If all that was OK, I'd discuss connectivity with the network team.

    ReplyDelete
  2. Apart from what Daveford said I would also look into any new indexes that might have been created, specially on columns with low cardinality, to see if that index is being used.

    Are you using DDS logical files to create those indexes? A DB2 upgrade might have switched the selected Optimization Engine from CQE to SQE, depending on the version you're running on.

    If all else fails, you could also try creating vector indexes to help the Optimizer choose the radix indexes.

    You can also try to see what was done to the machine around the time the query got slower to try and figure out if it was something unrelated to the query itself.

    Copy the data to your DEV database and compare the Visual Explain from PROD to DEV.

    Hope at least one of the suggestions helps you out ;)

    ReplyDelete
    Replies
    1. Hello Zen,

      Yes, it helps me alot, I've posted this query to get the suggestions from experts like you. Because we are beginners, we don't know much. Thanks for sharing your valuable inputs.

      Delete
  3. I could not find the default "follow" link provided by blogspot in your blog.
    And I do not use google+.
    Do not know how to follow you.

    ReplyDelete
    Replies
    1. You can follow my FB page: https://www.facebook.com/vinaysdb2blog/

      Delete

ads