Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to avoid round trips for store procedure with multiple result sets

Filip DraganovMar 13 2017 — edited Mar 14 2017

Hi,

my application uses a stored procedure which returns the output of fast queries through multiple (over 20) SYS_REFCURSOR parameters. I have the equivalent procedure in T-SQL as my application interfaces MsSQL Server too.

The application executes the procedure thousands of times in row (this is imposed by the business logic of my application). The problem is that the PL/SQL procedure is few times slower than the T-SQL procedure.

As far as I understand each SYS_REFCURSOR causes an additional round trip to the oracle database server. This doesn't happen with MsSQL Server.

Since my application initially supported only MsSQL I decided to use store procedures instead of queries exactly because I wanted to avoid redundant round trips.

My Question is how can I reduce the number of round trips when calling the store procedure?

Is there any other approach through which I can return multiple fast result sets.

Here is an example of my procedure

CREATE TABLE PRODUCT (

    PRODUCTID INT,

    PRODUCTDESCRIPTION NVARCHAR2(100),

    MISTERYITEMID INT

);

CREATE TABLE LOCATION (

    LOCATIONID INT,

    ADDRESS NVARCHAR2(200),

    MISTERYITEMID INT

);

CREATE TABLE EMPLOYEE (

    EMPLOYEEID INT,

    NAME NVARCHAR2(50),

    SIRNAME NVARCHAR2(50),

    MISTERYITEMID INT

);

CREATE OR REPLACE PROCEDURE SP_MULTISELECT (

    PAR_MISTERYITEMID INT,

    PAR_CUR_PRODUCT OUT SYS_REFCURSOR,

    PAR_CUR_LOCATION OUT SYS_REFCURSOR,

    PAR_CUR_EMPLOYEE OUT SYS_REFCURSOR

)

AS

BEGIN

    OPEN PAR_CUR_PRODUCT FOR

        SELECT PRODUCTID, PRODUCTDESCRIPTION, MISTERYITEMID FROM PRODUCT

        WHERE MISTERYITEMID = 1;

    OPEN PAR_CUR_LOCATION FOR

        SELECT LOCATIONID, ADDRESS, MISTERYITEMID FROM LOCATION

        WHERE MISTERYITEMID = 1;

    OPEN PAR_CUR_EMPLOYEE FOR

        SELECT EMPLOYEEID, NAME, SIRNAME, MISTERYITEMID FROM EMPLOYEE

        WHERE MISTERYITEMID = 1;

END;

FYI my application is running under 11gR2.

This post has been answered by Stew Ashton on Mar 14 2017
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 11 2017
Added on Mar 13 2017
25 comments
2,237 views