Wednesday, June 10, 2009

Keep the Result Set Small

You should aim to keep the result set small. This reduces both the amount of memory used in the database system and the network load when transferring data to the application server. To reduce the size of your result sets, use the WHERE and HAVING clauses.

Using the WHERE Clause

Whenever you access a database table, you should use a WHERE clause in the corresponding Open SQL statement. Even if a program containing a SELECT statement with no WHERE clause performs well in tests, it may slow down rapidly in your production system, where the data volume increases daily. You should only dispense with the WHERE clause in exceptional cases where you really need the entire contents of the database table every time the statement is executed.

When you use the WHERE clause, the database system optimizes the access and only transfers the required data. You should never transfer unwanted data to the application server and then filter it using ABAP statements.

Using the HAVING Clause

After selecting the required lines in the WHERE clause, the system then processes the GROUP BY clause, if one exists, and summarizes the database lines selected. The HAVING clause allows you to restrict the grouped lines, and in particular, the aggregate expressions, by applying further conditions.

Effect

If you use the WHERE and HAVING clauses correctly:

  • There are no more physical I/Os in the database than necessary
  • No unwanted data is stored in the database cache (it could otherwise displace data that is actually required)
  • The CPU usage of the database host is minimize
  • The network load is reduced, since only the data that is required by the application is transferred to the application server.

No comments:

Blog Archive