subscribe: Posts | Comments

Why is query not performing optimal?

0 comments

pic2

There can be more reasons why a query is not performing
optimal.

For basic understanding, they will be categorized in 4 main areas:

  • Doing too many IO operations

  • Using expensive operations on rows

  • Not utilizing the available resources (such as
    parallelization)

  • Processing too many rows

Experience has shown that a large part of non optimal
performing queries are due to the lack of performance to the 4th reason –
processing too many rows

There can be more reasons why a query is not performing
optimal.

For basic understanding, they will be categorized in 4 main areas:

  • Doing too many IO operations

  • Using expensive operations on rows

  • Not utilizing the available resources (such as
    parallelization)

  • Processing too many rows

Experience has shown that a large part of non optimal
performing queries are due to the lack of performance to the 4th reason –
processing too many rows

Most badly performing queries usually exhibit a high amount of rows processed
and then thrown away because they were not needed in the output.

There is a method to tune a query by eliminating throwaway.
Basic method for Performing Query Tuning

Below is a generic method for performing query tuning.

Basically tuning of queries can be done by following high level method:

  1. Set Goals

  2. Identify queries to be tuned

  3. For every query identified

  4. Analyze the query

  5. Suggest changes to improve performance

  6. Implement changes

  7. Test improvement

  8. Repeat until goal reached (or time spend)

Traditionally, the hardest part of this is to get from step 4
(‘analyze’) to step 5 (‘suggest changes’) – a lot of guesswork and/or experience
of the statement tuner