[SOLVED] Does the prepared SQL statements in a stored procedure make the performance better?

Does the prepared SQL statements in a stored procedure make the performance better?

I know there have already been lots of question about stored procedure vs prepared SQL statements, but I want to find out something different - if the prepared statements inside a procedure contribute to the performance of this stored procedure, which means make it better.

I have this question because I was told following points when searching some introduction of these 2 skills.

Stored procedure will store and compile your series of statements in db, which will reduce the overhead of transferring & compiling.
Prepare statements will be compiled and cached in db for multiple
access which lead to less overhead.

I am puzzled about these 'compile', 'store', and 'overhead' - a little bit abstract.

I use prepared statement to avoid re-parse if it will be called frequently. However should I use prepared statements (to cache & compile) inside a procedure? Since my procedure would have already been stored and compiled in DB, prepare something inside seems meaningless. (compile what was compiled?)

edit with sample code:

Create or Replace procedure MY_PROCEDURE
Begin
    //totally meaningless here?
    declare sqlStmt varchar(300);
    declare stmt statement;

    set sqlStmt='update MY_TABLE set NY_COLUMN=? where NY_COLUMN=?';
    prepare stmt from sqlStmt;
    execute stmt using 2,1
    execute stmt using 4,3
        ..............
END

Is the the above one better than below, since it only parse the statement once? Or same, because statements in procedure will have been pre-compiled.

Create or Replace procedure MY_PROCEDURE
Begin
    update MY_TABLE set NY_COLUMN=2 where NY_COLUMN=1;
    update MY_TABLE set NY_COLUMN=4 where NY_COLUMN=3;
        ..............
END

Solution

In DB2 actually the opposite may be true. Statements in an SQL routine are prepared when the routine is compiled. Dynamic SQL statements, as in your example, are prepared during the routine run time.

As a consequence, the preparation of dynamic statements will take into account the most current table and index statistics and other compilation environment settings, such as isolation level, while static statements will use the statistics that were in effect during the routine compilation or the latest bind.

If you want stable execution plans, use static SQL. If your statistics change frequently, you may want to use dynamic SQL (or make sure you rebind your routines' packages accordingly).

The same logic applies to Oracle PL/SQL routines, although the way to recompile static SQL differs -- you'll need to invalidate the corresponding routines.