Оптимизация производительности запросов в хранимой процедуре ⇐ C#

[Anonymous]

Я пытаюсь оптимизировать производительность запроса в Oracle, который занимал около 40–50 секунд, а в некоторых случаях — 1 минуту. После некоторого анализа я провел рефакторинг кода серверной части с помощью C# и в запросе сделал следующее:
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;

Причина, по которой я использовал две дополнительные переменные, чтобы избежать перехвата параметров: запрос выполняется за 4 секунды непосредственно в SQL Server, но занимает > 30 секунд в ASP.NET?. В моем случае это настольное приложение. Но это по-прежнему занимает около 26 секунд.
Есть ли способ повысить производительность запросов, скажем, до 10–20 секунд?
В C# я вызываю процедуру следующим образом:
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
{
}


Подробнее здесь: https://stackoverflow.com/questions/785 ... -procedure