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.
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.