Can somone confirm for me that, although PL/SQL improves performance for multiple database actions performed together, it will actually decrease performance over a direct query for a large query, say, over a thousand rows, that has nothing done to it except returning to the calling program? This must be the case, since the data is first loaded by basic sql processing, and then moved to buffers controlled by PLSQL, but I would like a confirmation of some sort.
In this case the calling program is a jdbc driver.
Thanks for any info. --Simmie
Uh, not that I'm aware of, and certainly not to an extent that it would invalidate the *multiple* advantages that use of an abstraction layer (in PL/SQL) enables. In PL/SQL, if you simply open a RefCur with the large SQL statement and return that to the calling proc, you have done practically nothing in PL/SQL *except* to keep the calling program from containing SQL, which has immense value. The overhead is extremely minimal. Now, if you *do* have an abstraction layer, the potential performance benefits that can be realized down the road can be enormous and certainly eclipse any minor overhead realized now.
Now, if you are using table parameters, your overhead will definitely be larger, but that's why one doesn't use table parameters any more. *Even if* you are still using table parameters to return result sets, the benefits of an abstraction layer still far outweigh the minor overhead incurred.