Performance - SQL

Achieving good performance is very important in server business logic. This section provides some useful guidelines and hints related to the SQL Performance.

This section describes commonly known performance issues that can be avoided by applying these relatively simply performance standards. The issues are not sorted by importance. The nature of performance related issues is such that there is no absolute fix that works for all cases. In some cases one of the solutions described below could be the best solution and in another case it could be better to go with some other solution. But if you suspect that the code you write or change, is critical for performance then you should discuss your proposed solution with a technical expert.

Contents

Here are some of the potential performance bottlenecks and the recommended solutions to avoid those

• Unnecessary Sorting, Grouping and Distinct
  Using distinct, order by or group by affects response time and should only be considered if necessary.
• Functions in Select and Where
  Use PL/SQL functions and packages carefully in your SQL statements, especially in WHERE clauses.
• Select *
  Only select the required columns when writing cursors for views or tables.
• Avoid translated columns in WHERE clause
  Avoid using translated values in where conditions, instead use the database values.
• USE EXISTS rather than IN
  when using IN is better than EXISTS, and vice versa

• Use IN rather than NOT IN
  when using IN is better than NOT IN

• Avoid Implicit Data Conversion in WHERE Clause
  Use Explicit conversion where possible

• Use SELECT INTO where possible
  Use SELECT INTO rather than OPEN FETCH Cursor where possible.

​

​