Procedures vs SQL

Hi

Will the performance be better when using procedures rather than using SQL in the programming codes? :rolleyes:

depends how good your code is doesnt it

yes (and maybe no :p).

Generally in my experiance yes. passing SQL with variables (or calling procedures and passing it varialbes) will be more efficient than just hitting the database with SQL containing literals.

This kind of explains it

I'm sure there are hundreds of other reasons that my tiny mind doesn't grasp :)

and who said you cannot use bind variables using plain SQL...?!

in case of store procedure ,u can keep the pkg in shared pool using dbms_shared_pool . performance will be better.

Quote:

---

Originally posted by pando
and who said you cannot use bind variables using plain SQL...?!

---

good point...I seem to be answering a different question :rolleyes:

A stored procedure is pre-loaded in memory for faster execution. It allows the DBMS control of permissions for security purposes. It also eliminates the need to recompile components when minor changes occur to the database.

If you can do it in a single statement, do it - This is faster than any other method.
If you can't, then do it in PL/SQL procedure.

Tamil

If the SQL is a joined query or it involves a few tables, so does it helps to use a procedure even though it can be written in a single statement? :D