Re: Stored Proc SLOWER than embedded sql
Just one last note regarding this..
I think I noticed too that the PREPARE syntax is slower than you would expect it to be.
There are some notes on that in the manual, saying that there's actually a difference between PREPARE from inside a SQL or PSM context versus prepared statements via a library. And the manual also states that the SQL/PSM PREPARE is meant just for testing prepared statements, not really to build something with it
Of course, everybody's using PREPARE because it's the only way to perform dynamic sql (in my opinion, the statement preparation and dynamic SQL are independant concepts, which just 'happen' to be intermingled). And it would be cool if the PREPARE would be as fast or faster even than ordinary statements (it should be, at least in the prepare-once-execute-many case).
Having said that, my hunch is that ordinary stored procedures, especially if they perform some complex logic, involving cursors, nested procedure calls etc. will be faster than whatever you can come up with from a client context.
It's just a matter of network logistics. One stored procedure call is one network roundtrip, doing it from the client context multiple. If the response would involve data (which you can now handle using a server side cursor) the difference would be even bigger.
Anyone to confirm or reject this?
Edited 1 time(s). Last edit at 01/17/2006 02:25AM by Roland Bouman.