PLSQL and large simple queries
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: PLSQL and large simple queries

  1. #1
    Join Date
    Jan 2002
    Posts
    1
    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

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width