Harnessing SQL 

Relational databases and the SQL language offer superior capabilities to filter large amounts of information. The SQL interpreter is directly located at the data source, so database operations are executed much faster than database operations from a connected application, like a Java program using JDBC.

In order to improve execution times and save memory it is mandatory to delegate every possible processing work to the database system. Use the following SQL features to improve the performance significantly:

·        Joins

·        Sub queries

·        Sorting

·        Column functions

Keep in mind that complex SQL statements may be demanding for the database. For more details and background information on database performance review the SAP help at:

http://help.sap.com/ and search for the terms:

”SQL Performance”, ”Database Performance” or “Open SQL”.

 

Example of a not recommended implementation:

A Java program scans the entire table to find out when a user performed his latest activity.

String query = "SELECT * from ActivityLog WHERE User = '"          + value + ´"'";      long timestamp = 0;      ResultSet rs = _dbAccess.query(query);      while (rs.next()) {         timestamp = rs.getLong("TIMESTAMP");      }      return timestamp;

The example has following flaws:

·        The database is queried to return all columns (SELECT *) although only the TIMESTAMP column is needed.

·        A Java iteration instead of the column functions of SQL that iterates over an entire column is used, to find the latest entry.

Example of a recommended implementation:

String query = "SELECT MAX(TIMESTAMP) FROM ActivityLog WHERE User = '"      + value + ´"'";   ResultSet rs = null;   try {      rs = _dbAccess.query(query);      if(rs.next()) {         return rs.getLong(1);      } else {         return -1;      }   } finally {   // see section Resource Management      if(rs != null) {           rs.close();      }   }

 

The columns referenced by the WHERE or the ORDER BY clause should have an index.