Overview
An SQL may perform reasonably well even if not efficiently written in cases where the volume of processed data is low, like in a development environment. However, the same SQL may perform very poorly when executed in a real high volume environment. Therefore, any SQL should be carefully checked to make sure it would provide reasonable performance at execution time.
Obviously there could be many reasons for an SQL to perform poorly and not all of them are easy to predict or track.
In general, these could be subcategorized into two main groups:
• Basic issues related to the SQL code. These may be missing JOIN statements, inefficient path to the desired data, inefficient use of database functions, etc.
• More complicated issues having to do with lack of indexes, database tuning and handling of high volume of data, efficiency of I/O system etc.
The latter group of issues may only be truly tested on a designated environment simulating a real production configuration. These performance tests are typically conducted by a team of database and operating system experts as part of a thorough performance testing of a predefined set of process.
It is the first group of issues that can and should be tested by the programmer at this stage. This is done by analysis of the SQL's Explain Plan result.