What is the stored procedures performance compared to embedded SQL in various RDBMs?

Question

From my experience with DB2 on Z/OS there is no difference between embedded SQL and stored procedures as both get compiled to native code. On the other hand I know that in Oracle there is a huge difference - is it true and how is it in other DBs? Please provide some links to support your claims.

Answer 1

Embedded SQL in Oracle means that you can embed SQL statements right into your C/C++ code. There is a tool called Pro*C that parses these statements and wraps them into OCI calls.

This is of course a client side tool, I don't see how it may be compared to a server-side stored procedure.

If by "embedded SQL" you mean anonymous blocks, then there is some performance decrease when a block is called for the first time, because is needs to be compiled. Stored procedured are kept precompiled in the database.

As soon as the anonymous block is called for the first time and kept in the cache, there is no performance difference.

P. S. There is thing called native compilation in PL/SQL that allows compiling the procedures and storing them in the shared libraries on the disk.

That can only be applied to stored procedures, but it's only useful for CPU intensive tasks, not for complex SQL queries.