4 Replies Latest reply: Jan 22, 2014 5:50 AM by Billy~Verreynne RSS

    Issue when calling Stored procedure from application

    imran khan

      Dear all,

       

        Oracle DB version: 11.2.0.3

         OS : RHEL 5.9 64-bit version

       

       

        We are trying to execute one of the stored procedure (belonging to oracle DB schema) from our Dotnet application(recites in application server) but it takes more than 30 seconds to fetch the records. The stored procedure is called from application. When we try the same procedure from SQL developer or TOAD it completes within a second. Could anyone suggest on the issue?

       

       

      Note: we are calling a remote DB view from our DB.

       

      No. of records in the view : 49484929 rows

       

       

       

      Thanks in advance,

       

      Imran Khan

        • 1. Re: Issue when calling Stored procedure from application
          Billy~Verreynne

          You need to explain the input and output parameters of the stored proc, describe what the procedure does, and detail how the .Net call is made.

          • 2. Re: Issue when calling Stored procedure from application
            imran khan

            Thanks Billy for the reply. The following is the function of the .net application which calls the procedure:

             

             

            public DataSet ResourceMonitor(String Date, String DateTime, String starttime, String endtime, String Assstarttime, String Assendtime)

             

            try

             

            cLog.StartMethod(this, System.Reflection.MethodInfo.GetCurrentMethod().Name);

             

            Database dbResourceMonitor = null;

             

            DataSet Resourceset = new DataSet();

             

            dbResourceMonitor = DatabaseFactory.CreateDatabase();

             

            DbCommand dbCommand = dbResourceMonitor.GetStoredProcCommand("SP_RESOURCEMONITOR1");

             

            dbResourceMonitor.AddInParameter(dbCommand, "P_Date", DbType.String, Date);

             

            dbResourceMonitor.AddInParameter(dbCommand, "P_DateTime", DbType.String, DateTime);

             

            dbResourceMonitor.AddInParameter(dbCommand, "P_Starttime", DbType.String, starttime);

             

            dbResourceMonitor.AddInParameter(dbCommand, "P_Endtime", DbType.String, endtime);

             

            dbResourceMonitor.AddInParameter(dbCommand, "P_AssStarttime", DbType.String, Assstarttime);

             

            dbResourceMonitor.AddInParameter(dbCommand, "P_AssEndtime", DbType.String, Assendtime);

             

             

             

            //dbCommand.Parameters.Add(BuildRefCursorParameter("DEPRECSET"));

             

            dbCommand.Parameters.Add(BuildRefCursorParameter("RECORDSET1"));

             

            dbCommand.Parameters.Add(BuildRefCursorParameter("RECORDSET2"));

             

            dbCommand.Parameters.Add(BuildRefCursorParameter("RECORDSET3"));

             

            dbCommand.Parameters.Add(BuildRefCursorParameter("RECORDSET4"));

             

            return Resourceset;

             

            catch (Exception ex)

             

            cLog.LogMessages(this, ex.Message, cLog.eWiproMessageTypes.ErrorType);

             

            throw ex;

             

            finally

             

            cLog.EndMethod(this, System.Reflection.MethodInfo.GetCurrentMethod().Name);

             

             

               The stored procedure SP_RESOURCEMONITOR1 has the following DDL:

             

            CREATE OR REPLACE PROCEDURE SP_RESOURCEMONITOR1

            (     P_Date      IN VARCHAR2,     

                  P_DateTime      IN VARCHAR2,     

                  P_Starttime     IN VARCHAR2,

                  P_Endtime     IN VARCHAR2,

                  P_AssStarttime     IN VARCHAR2,

                  P_AssEndtime     IN VARCHAR2,

                   P_Mode     IN NUMBER,

                RECORDSET1 OUT SYS_REFCURSOR,

                RECORDSET2 OUT SYS_REFCURSOR,

                RECORDSET3 OUT SYS_REFCURSOR,

                RECORDSET4 OUT SYS_REFCURSOR

            )AS

            BEGIN

             

             

            OPEN RECORDSET1 FOR

            Select ROLEMASTER.ROLEID,ROLEMASTER.ROLENAME as ResourceGroup,departmentmaster.DEPARTMENTNAME,departmentmaster.departmentid from Rolemaster

            INNER JOIN departmentmaster ON Rolemaster.DEPTID=departmentmaster.DEPARTMENTID

            WHERE 1=1 AND sysdate >=Rolemaster.START_DATE AND sysdate<=Rolemaster.END_DATE AND Upper(RoleMaster.Rolename) not like '%HIGHLOADER%' AND displayinlist=1

            ORDER BY ROLENAME;

             

             

            OPEN RECORDSET2 FOR

             

             

            select ROM.ROLENAME as ResourceGroup, fn_get_resource_status(p_starttime, p_endtime,RM.Resourceid,RM.DeptID) AS Status,upper(RM.Resourceid)as Resourceid,RM.RoleID,RM.DeptID,TRIM(RM.RESOURCENAME) AS RESOURCENAME,'Detail'As Details,'ResChng'As ResChng,'Calender' As Calender FROM ResourceMaster RM

            --INNER JOIN DEPARTMENTMASTER DM ON RM.DEPTID = DM.DEPARTMENTID

            INNER JOIN ROLEMASTER ROM ON RM.ROLEID = ROM.ROLEID

            where --UPPER (DM.DEPARTMENTNAME) IN ('DISPATCH','TRANSPORT','TRANSPORT RAMP')

            RM.DEPTID in (select DEPARTMENTID from DEPARTMENTMASTER where displayinadmin=1 and active=1)  ;

             

             

            if (P_Mode =2) THEN

            OPEN RECORDSET3 FOR

            select * from mobility_attendance_info_v where TRANSACTIONDATE >= TO_DATE(Substr(p_starttime,1, 9) ,'dd-MON-rr') - 1  order by staffno,TRANSACTIONDATETIME Desc;

            END IF;

             

             

            OPEN RECORDSET4 FOR

            select rsa.*,planning.flightno,'' As StTime,SUBSTR(rsa.depdt,11,5) as EndTime from resourceallocation rsa inner join planning ON rsa.planningid = planning.planningid

            where TO_TIMESTAMP(rsa.DEPDT,'dd-MON-rr HH24:MI')>=TO_TIMESTAMP(P_DateTime,'dd-MON-rr HH24:MI')  ;

             

             

            END SP_RESOURCEMONITOR1;

            /

             

               The view mobility_attendance_info_v  is a called from a remote DB. Hope it's quite clear.

             

             

            Regards,

            Imran Khan

            • 3. Re: Issue when calling Stored procedure from application
              imran khan

              Could anyone help me out resolving the issue..

               

               

              Thanks!

              Imran Khan

              • 4. Re: Issue when calling Stored procedure from application
                Billy~Verreynne

                Nothing in principle wrong.

                 

                Bind variables are used. Ref cursors are used. Explicit data conversion of string to date is used. All good.

                 

                The questionable items are, why not use date parameters instead of strings? This results in a more robust call interface as it deals with the parameter values using the appropriate data types.

                 

                The UPPER() and TO_TIMESTAMP() functions are used in SQL predicates on table columns. This applies a conversion to the column, rendering any potential index on that column useless - except if that index applies the same conversion (usually not).

                 

                There is also a conceptual issue. The ref cursors are called "record sets" by the variables used. A cursor is not a record set. A cursor is in fact executable code (see cursor's execution plan for the design of this program). A cursor outputs data. It is not a result set of data that is created and stored in server memory for use by the client. Such an approach will place a huge demand on server memory and would not scale.

                 

                As for comparing performance of calling the procedure from SQL*Plus/TOAD or .Net.

                 

                The procedure creates the cursor "programs". It does not execute them. So a call to the procedure will be relative fast as the only work done is a soft or hard parse to create the required cursor - and then pass the (reference) handle of that cursor to the caller.

                 

                The caller will now use this cursor handle - and each "use" (e.g. fetch from the cursor) instructs the server to execute the cursor program, find the required row data, and output the row data to the caller to consume.

                 

                So if you test this from SQL*Plus/TOAD, you need to execute the cursor handle (execute the cursor program on server) and consume the output returned (done using the "print" command in SQL*Plus).

                 

                Also, testing from one client and comparing that to the same test on another client can also differ in elapsed execution time. Two basic reasons. Size of the fetch array (determines how optimal performance is transporting server output to client). And whether or not the database server used faster logical I/O when executing the cursor, or whether the server had to use slower physical I/O to read the data from disk as it was not in the buffer cache at that point in time.