I am trying to optimize a query performance in Oracle that took around 40 – 50 secs, in some cases it took 1 min. After some analysis, I refactored code in the backend with C# and in the query, did something as follows:

CREATE OR REPLACE PROCEDURE GetData_Rewrite 
(
  p_Id1  IN NUMBER,
  p_Id2 IN NUMBER,
  o_ResultSet OUT SYS_REFCURSOR
) AS

 v_MyParam1 NUMBER;
 v_MyParam2 NUMBER;

BEGIN
v_MyParam1 := p_Id1;
v_MyParam2 := p_Id2;

  OPEN o_ResultSet FOR
    SELECT * FROM (
      SELECT
         C.CONTRACTD_TYPE_RANK,
         C.CONTRACT_SET_ID,
         NVL(cte.Contract_type_Desc, CT.CONTRACT_TYPE_NAME) AS CONTRACT_TYPE_NAME,
         C.CONTRACT_SET_NAME,
         C.TERM_GROUP_NAME,
         C.LAST_UPDATE_DATE,
         C.CALC_START_DATE,
         CASE C.CALC_END_DATE WHEN TO_DATE('01-01-3000', 'dd-mm-yyyy') THEN NULL ELSE C.CALC_END_DATE END AS CALC_END_DATE,
         C.CALC_NUM_ROWS,
         C.EDIT_STATUS_CODE AS EDIT_STATUS_CODE
      FROM
        (SELECT
          cs.CONTRACT_SET_NAME,
          cs.enterprise_id,
          MAX(cd.GROUP_NAME) AS TERM_GROUP_NAME,
          MAX(cd.UPDATE_DATE) AS LAST_UPDATE_DATE,
          MIN(cd.START_DATE) AS CALC_START_DATE,
          MAX(NVL(cd.END_DATE, TO_DATE('01-01-3000', 'dd-mm-yyyy'))) AS CALC_END_DATE,
          COUNT(*) AS CALC_NUM_ROWS,
          cd.CONTRACTD_TYPE_RANK,
          cd.CONTRACT_SET_ID,
          MAX(
            CASE
              WHEN NVL(ced.EDIT_STATUS, 'Original') = 'Original' THEN 0
              WHEN NVL(ced.EDIT_STATUS, 'Original') = 'New' THEN 1
              WHEN NVL(ced.EDIT_STATUS, 'Original')  = 'Deleted' THEN 2
              WHEN INSTR(NVL(ced.EDIT_STATUS, 'Original'), 'Locked') = 1 THEN 3
              WHEN NVL(ced.EDIT_STATUS, 'Original') = 'Changed' THEN 4
              ELSE 0
            END
          ) AS EDIT_STATUS_CODE
        FROM CNTR_CONTRACTD cd
        JOIN CNTR_CONTRACT_SET cs ON (cd.CONTRACT_SET_ID = cs.CONTRACT_SET_ID AND cd.enterprise_id = cs.enterprise_id)
        LEFT JOIN CNTR_EDIT_DET ced ON (cd.CONTRACT_DETAIL_ID = ced.CONTRACT_DETAIL_ID)
        WHERE cs.COL1 = v_MyParam1
          AND cd.COL2 = v_MyParam2
        GROUP BY
          cs.CONTRACT_SET_NAME,
          cs.enterprise_id,
          UPPER(cd.GROUP_NAME),
          cd.CONTRACTD_TYPE_RANK,
          cd.CONTRACT_SET_ID
        ) C
      JOIN CNTR_CONTRACTD_TYPE CT ON (C.CONTRACTD_TYPE_RANK = CT.CONTRACTD_TYPE_RANK)
      LEFT JOIN CNTR_CONTRACTD_TYPE_ENT CTE ON (CTE.CONTRACTD_TYPE_ID = CT.CONTRACTD_TYPE_ID AND CTE.enterprise_id = C.enterprise_id)
    )
    ORDER BY CONTRACT_TYPE_NAME, CONTRACT_SET_NAME, TERM_GROUP_NAME;
END GetContractInfos_Rewrite;

The reason I used two extra variables to avoid parameter sniffing – Query executes in 4 seconds directly in SQL Server but takes > 30 seconds in ASP.NET?. In my case, it’s a desktop app. But it still takes around 26 secs.

Is there any way to improve the query performance say to 10 – 20 secs?

C# – In C#, Calling the procedure as follows:

try 
{
   using(OracleCommand cmd = (OracleCommand) mHibernateSession.Connection.CreateCommand()) 
   {
     cmd.CommandType = CommandType.StoredProcedure;
     cmd.CommandText = "PACKAGE_NAME.GetData_Rewrite";

     cmd.Parameters.Add("p_Id1", val1);
     cmd.Parameters.Add("p_Id2", val2);

     OracleParameter paramCursor = new OracleParameter("o_ResultSet", OracleDbType.RefCursor, 2000,
       ParameterDirection.Output, true, 0, 0, string.Empty, DataRowVersion.Default, DBNull.Value);
     cmd.Parameters.Add(paramCursor);

     using(OracleDataAdapter adapter = new OracleDataAdapter(cmd)
     {
       adapter.ReturnProviderSpecificTypes = false;
       adapter.Fill(dtRows);
     }
   }
 } 

 finally 
 {

 }