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.