SQL Optimizer for SAP ASE 3.9 - Installation Guide

Common Coding Errors in SQL Statements

The performance of database applications can be downgraded simply because common coding errors existing in SQL statements introduced by database changes such as adding a new index. Therefore it is important to review every SQL statement existing on the database server and source code using the SQL Scanner module. Performance of SQL statements can normally be dramatically improved by correcting common coding errors based on database information obtained during login.

The SQL Optimizer not only optimizes the input SQL statements but also corrects common coding errors based on database information obtained during login.

The followings are some examples of common mistakes found in SQL statements:

Enable index search (Built-in De'Morgan Law Engine is used to solve this problem)

SELECT *

FROM table_a  

WHERE NOT(table_a.key + 5) > 15 

SELECT *

FROM table_a  

WHERE table_a.key <= 15 - 5  

Remove unnecessary function calls (If A.key is a not null column detected from database)

SELECT *

FROM table_a  

WHERE ISNULL(table_a.key,0) = 10 

SELECT *

FROM table_a  

WHERE table_a.key = 10 

Related Topic

Optimization Engine