PL/SQL Programming Tips/Code Tuning

Things to remember while writing PL/SQL code:

• Always write a optimized Code.Some might think it is optimized based on length of the code. But one should take care that optimized  code does not depend on how small or big are the lines of code,but it all depends of the performance of the code snippet. 
• The performance can be considered based on the execution time it takes. The more minimum time taken is the well performed program.
• Below are some code tuning tip,

      1)Always prefer >=,<= rather than using BETWEEN function

      2)If NULL conditions are need to be checked in IF blocks, always use IS NULL in IF clause, rather than using IS NOT NULL in IF clause. Because performing NOT function take some amount of execution time.

          Eg: 1)IF var1 IS NULL  THEN                      2) IF var1 IS NOT NULL THEN                

                    ------------                                                   -----------------------
                   ELSE                                                         ELSE
                    -------------                                                -------------------------
                    END IF;                                                   END IF;
         

         In the above examples, eg1 performs faster than eg2

      3)Do not use COMMIT in any loops of PL/SQL coding unless each record needs to be committed at that instance,

•  For example if there are 100 records and all of them needs to be committed when all of them    are  valid only. In that case we can write the COMMIT outside the LOOP. 
•  Because  COMMIT will consume some time to perform that operation for each record. 
•  And if the requirement is like we need to store each record immediately and skip the INVALID  record we can use COMMIT inside. Use it based on the requirement given.