Why SQL Query runs Slowly?

Introduction 

Query tuning is the process by which you answer the question "How can I make this query run faster?"

There are many possible reasons why an SQL query runs slowly.

• The system may simply not have enough processing power, memory, or disk bandwidth to handle the application load.

• There may be a problem with the system configuration, such as insufficient disk cache, MX buffer space, or kernel managed swap file space.

• The files used to store the database tables may be badly fragmented. Such "system-level" problems are outside the scope of query tuning, and must be addressed separately before query tuning can be effective.

The query tuning process treats the query text as the sole statement of the problem. Its goal is to produce an execution plan that runs the query faster, while returning the same set of rows as before.

Why Queries run slowly?

From a business process perspective, most reasonable queries will return a moderate number of rows.

For a reasonable OLTP query, the maximum number of rows returned is around one hundred or so (and is often much less than this).

The user of an interactive query doesn't want to browse through many more rows than this.

Even for a reasonable batch or reporting query, the user does not want to browse through more than a few hundred (or very few thousand) or so rows.

If a batch-type query returns more than a few thousand rows, it needs to be broken down into multiple more closely filtered queries, perhaps aimed at multiple classes of user. Modern NonStop systems are quite capable of executing a query that returns a few hundred (even a few thousand) rows in a reasonably short time.

So the question arises;

Why does a query that returns a modest number of rows take too long to run? One answer is that the query accesses and processes too many rows that are eventually discarded, and don't contribute to the final query result.

The goal in this case is to avoid performing such non useful work.

Another such class of query is one that returns a reasonable number of rows, but which is required to aggregate a very large number of rows in order to produce the required results.

A query containing a GROUP BY clause is a common example of this. These queries can be difficult to tune. A common solution is to preaggregate the data in the database, and to query the summary data without accessing the details. The careful use of triggers can be used to synchronize the redundant summary data.