Oracle Ref Cursor Issue in ADO.NET

[Ram]

Hello,

I ran into a performance issue with Oracle Ref Cursor in ADO.NET (with
VS 2003). The SQL itself runs in less than 5 seconds in SQL*Plus.
(Oracle 9i). When the same is called from ADO.NET using the microsoft
provider for oracle (system.data.oracleclient.dll version
1.1.4322.2032), the fill method takes upto 5 minutes to fill the
dataset. The result set returned is less than 100 rows and the row size
is less than 200 characters. Any ideas why this might be happening.

 

[Paul Clement]

¤ Hello,
¤
¤ I ran into a performance issue with Oracle Ref Cursor in ADO.NET (with
¤ VS 2003). The SQL itself runs in less than 5 seconds in SQL*Plus.
¤ (Oracle 9i). When the same is called from ADO.NET using the microsoft
¤ provider for oracle (system.data.oracleclient.dll version
¤ 1.1.4322.2032), the fill method takes upto 5 minutes to fill the
¤ dataset. The result set returned is less than 100 rows and the row size
¤ is less than 200 characters. Any ideas why this might be happening.

What does the Oracle PL/SQL look like?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Ram]

Hello Paul,

This is a standard SQL selects that return the results as a refcursor
back to ADO.NET.

Following is the sample:

PROCEDURE USP_Dept_Prod_Report
(categoryId IN varchar,
businessUnitLabelId IN varchar,
employeeIdentifier IN varchar,
departmentNumber IN varchar,
fromActivityDate IN varchar,
toActivityDate IN varchar,
activityNumber IN varchar,
deptProdData OUT USP_REPORTDATA_PKG.dataCursor) IS
BEGIN
Open deptProdData FOR
SELECT dist.DEPT_NUM,
dept.DEPT_NM,
lbr.LBR_GRP_CD,
lbr.LBR_GRP_NM,
dist.PAYROLL_EMP_REF_NUM EMP_ID,
emp.EMP_FIRST_NM || ' ' || emp.EMP_LAST_NM
EMP_NM,
dist.LWSN_ACTVT_NUM,
actvt.LWSN_ACTVT_DESC,
ROUND(SUM(NVL(dist.PREM_TM, 0) /60), 2)
PREM_TM,
ROUND(SUM(NVL(dist.TRAVEL_TM, 0) /60), 2)
TRAVEL_TM,
ROUND(SUM(NVL(dist.PREM_DLR_AMT, 0)), 2)
PREM_DLR_AMT,
ROUND(SUM(NVL(dist.TRAVEL_DLR_AMT, 0)), 2)
TRAVEL_DLR_AMT,
ROUND(SUM(NVL(dist.PREM_DLR_AMT, 0) +
NVL(dist.TRAVEL_DLR_AMT, 0)), 2) TOT_DLR_AMT
FROM DIST_DATA dist, EMP emp, LWSN_ACTVT actvt,
DEPT dept, LBR_GRP lbr
WHERE dist.PAYROLL_EMP_REF_NUM =
emp.PAYROLL_EMP_REF_NUM
and (dist.REC_STS_CD = 'POSTED' OR
dist.REC_STS_CD = 'OKAY' OR REC_STS_CD = 'WARNING')
and dist.LWSN_ACTVT_NUM = actvt.LWSN_ACTVT_NUM
and dist.DEPT_NUM = dept.DEPT_NUM
and dist.JOB_CLS_CD = lbr.JOB_CLS_CD
and dist.DEPT_NUM = lbr.DEPT_NUM
and dist.actvt_dt between
to_date(fromActivityDate, 'MM/DD/YYYY') and to_date(toActivityDate,
'MM/DD/YYYY')
GROUP BY dist.DEPT_NUM,
dept.DEPT_NM,
lbr.LBR_GRP_CD,
lbr.LBR_GRP_NM,
dist.PAYROLL_EMP_REF_NUM,
emp.EMP_FIRST_NM || ' ' || emp.EMP_LAST_NM,
dist.LWSN_ACTVT_NUM,
actvt.LWSN_ACTVT_DESC;
END USP_Dept_Prod_Report;

When the SQL is ran SQL*Plus, the results are returned (less than 100
rows) in less than 5 seconds.

----Ram

 

[David Browne]

What SQL runs in 5 Seconds?

If it doesn't invoke this procedure using a PL/SQL block, then you're doing
something very different.

David


Hello Paul,

This is a standard SQL selects that return the results as a refcursor
back to ADO.NET.

Following is the sample:

PROCEDURE USP_Dept_Prod_Report
(categoryId IN varchar,
businessUnitLabelId IN varchar,
employeeIdentifier IN varchar,
departmentNumber IN varchar,
fromActivityDate IN varchar,
toActivityDate IN varchar,
activityNumber IN varchar,
deptProdData OUT USP_REPORTDATA_PKG.dataCursor) IS
BEGIN
Open deptProdData FOR
SELECT dist.DEPT_NUM,
dept.DEPT_NM,
lbr.LBR_GRP_CD,
lbr.LBR_GRP_NM,
dist.PAYROLL_EMP_REF_NUM EMP_ID,
emp.EMP_FIRST_NM || ' ' || emp.EMP_LAST_NM
EMP_NM,
dist.LWSN_ACTVT_NUM,
actvt.LWSN_ACTVT_DESC,
ROUND(SUM(NVL(dist.PREM_TM, 0) /60), 2)
PREM_TM,
ROUND(SUM(NVL(dist.TRAVEL_TM, 0) /60), 2)
TRAVEL_TM,
ROUND(SUM(NVL(dist.PREM_DLR_AMT, 0)), 2)
PREM_DLR_AMT,
ROUND(SUM(NVL(dist.TRAVEL_DLR_AMT, 0)), 2)
TRAVEL_DLR_AMT,
ROUND(SUM(NVL(dist.PREM_DLR_AMT, 0) +
NVL(dist.TRAVEL_DLR_AMT, 0)), 2) TOT_DLR_AMT
FROM DIST_DATA dist, EMP emp, LWSN_ACTVT actvt,
DEPT dept, LBR_GRP lbr
WHERE dist.PAYROLL_EMP_REF_NUM =
emp.PAYROLL_EMP_REF_NUM
and (dist.REC_STS_CD = 'POSTED' OR
dist.REC_STS_CD = 'OKAY' OR REC_STS_CD = 'WARNING')
and dist.LWSN_ACTVT_NUM = actvt.LWSN_ACTVT_NUM
and dist.DEPT_NUM = dept.DEPT_NUM
and dist.JOB_CLS_CD = lbr.JOB_CLS_CD
and dist.DEPT_NUM = lbr.DEPT_NUM
and dist.actvt_dt between
to_date(fromActivityDate, 'MM/DD/YYYY') and to_date(toActivityDate,
'MM/DD/YYYY')
GROUP BY dist.DEPT_NUM,
dept.DEPT_NM,
lbr.LBR_GRP_CD,
lbr.LBR_GRP_NM,
dist.PAYROLL_EMP_REF_NUM,
emp.EMP_FIRST_NM || ' ' || emp.EMP_LAST_NM,
dist.LWSN_ACTVT_NUM,
actvt.LWSN_ACTVT_DESC;
END USP_Dept_Prod_Report;

When the SQL is ran SQL*Plus, the results are returned (less than 100
rows) in less than 5 seconds.

----Ram

 

[Paul Clement]

¤ Hello Paul,
¤
¤ This is a standard SQL selects that return the results as a refcursor
¤ back to ADO.NET.
¤
¤ Following is the sample:
¤
¤ PROCEDURE USP_Dept_Prod_Report
¤ (categoryId IN varchar,
¤ businessUnitLabelId IN varchar,
¤ employeeIdentifier IN varchar,
¤ departmentNumber IN varchar,
¤ fromActivityDate IN varchar,
¤ toActivityDate IN varchar,
¤ activityNumber IN varchar,
¤ deptProdData OUT USP_REPORTDATA_PKG.dataCursor) IS
¤ BEGIN
¤ Open deptProdData FOR
¤ SELECT dist.DEPT_NUM,
¤ dept.DEPT_NM,
¤ lbr.LBR_GRP_CD,
¤ lbr.LBR_GRP_NM,
¤ dist.PAYROLL_EMP_REF_NUM EMP_ID,
¤ emp.EMP_FIRST_NM || ' ' || emp.EMP_LAST_NM
¤ EMP_NM,
¤ dist.LWSN_ACTVT_NUM,
¤ actvt.LWSN_ACTVT_DESC,
¤ ROUND(SUM(NVL(dist.PREM_TM, 0) /60), 2)
¤ PREM_TM,
¤ ROUND(SUM(NVL(dist.TRAVEL_TM, 0) /60), 2)
¤ TRAVEL_TM,
¤ ROUND(SUM(NVL(dist.PREM_DLR_AMT, 0)), 2)
¤ PREM_DLR_AMT,
¤ ROUND(SUM(NVL(dist.TRAVEL_DLR_AMT, 0)), 2)
¤ TRAVEL_DLR_AMT,
¤ ROUND(SUM(NVL(dist.PREM_DLR_AMT, 0) +
¤ NVL(dist.TRAVEL_DLR_AMT, 0)), 2) TOT_DLR_AMT
¤ FROM DIST_DATA dist, EMP emp, LWSN_ACTVT actvt,
¤ DEPT dept, LBR_GRP lbr
¤ WHERE dist.PAYROLL_EMP_REF_NUM =
¤ emp.PAYROLL_EMP_REF_NUM
¤ and (dist.REC_STS_CD = 'POSTED' OR
¤ dist.REC_STS_CD = 'OKAY' OR REC_STS_CD = 'WARNING')
¤ and dist.LWSN_ACTVT_NUM = actvt.LWSN_ACTVT_NUM
¤ and dist.DEPT_NUM = dept.DEPT_NUM
¤ and dist.JOB_CLS_CD = lbr.JOB_CLS_CD
¤ and dist.DEPT_NUM = lbr.DEPT_NUM
¤ and dist.actvt_dt between
¤ to_date(fromActivityDate, 'MM/DD/YYYY') and to_date(toActivityDate,
¤ 'MM/DD/YYYY')
¤ GROUP BY dist.DEPT_NUM,
¤ dept.DEPT_NM,
¤ lbr.LBR_GRP_CD,
¤ lbr.LBR_GRP_NM,
¤ dist.PAYROLL_EMP_REF_NUM,
¤ emp.EMP_FIRST_NM || ' ' || emp.EMP_LAST_NM,
¤ dist.LWSN_ACTVT_NUM,
¤ actvt.LWSN_ACTVT_DESC;
¤ END USP_Dept_Prod_Report;
¤
¤ When the SQL is ran SQL*Plus, the results are returned (less than 100
¤ rows) in less than 5 seconds.
¤

Are you running this as a stored proc or as SQL command text? Running it as SQL command text would
be the only thing I can think of that would cause a performance problem in this instance.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Ram]

This is being run as the command type = stored procedure. The issue
seems to be in binding the refcursor back into ADO.NET

 

[David Browne]

This is being run as the command type = stored procedure. The issue
seems to be in binding the refcursor back into ADO.NET

When you say "When the SQL is ran SQL*Plus, the results are returned (less
than 100
rows) in less than 5 seconds.", how are you running it SQL*Plus? Are you
using a ref cursor in SQL*Plus too, or just running the SQL query?

David

 

[Frans Bouma [C# MVP]]

This is being run as the command type = stored procedure. The issue
seems to be in binding the refcursor back into ADO.NET

I can't see the original posting of the thread, so forgive me if this
info was already posted earlier, but in both ODP.NET and MS Oracle
provider you can bind output parameters which are of type REF CURSOR to
datatables using the OracleDataAdapter. The ODP.NET provider comes with
examples which show you how to do this. It's very easy, as everything
is done for you. Typical example: (ODP.NET)

/// <summary>
/// Calls the specified retrieval stored procedure in the Oracle
database. Fills the
/// specified DataSet. Will participate in the transaction if a
transaction is in progress.
/// </summary>
/// <param name="storedProcedureToCall">Stored procedure to call</param>
/// <param name="parameters">array of parameters to specify</param>
/// <param name="dataSetToFill">DataSet to fill by the stored
procedure</param>
/// <returns>true if succeeded, false otherwise</returns>
public virtual bool CallRetrievalStoredProcedure(string
storedProcedureToCall, OracleParameter[] parameters, DataSet
dataSetToFill)
{
DynamicQueryEngine dqe =
(DynamicQueryEngine)CreateDynamicQueryEngine();
string procName =
dqe.GetNewPerCallStoredProcedureName(storedProcedureToCall);
procName = DynamicQueryEngine.GetNewStoredProcedureName(procName);
OracleCommand command = new OracleCommand(procName);
command.Connection = (OracleConnection)base.GetActiveConnection();
if(base.IsTransactionInProgress)
{
((IDbCommand)command).Transaction =
(OracleTransaction)base.PhysicalTransaction;
}
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = base.CommandTimeOut;

for(int i=0;i<parameters.Length;i++)
{
command.Parameters.Add(parameters);
}

try
{
base.OpenConnection();
command.ExecuteNonQuery();

// for each cursor parameter, create a datatable in dataset and fill
it.
using(OracleDataAdapter adapter =
(OracleDataAdapter)CreateNewPhysicalDataAdapter())
{
for (int i = 0; i < parameters.Length; i++)
{
if(parameters.OracleDbType == OracleDbType.RefCursor)
{
DataTable tableToFill =
dataSetToFill.Tables.Add(parameters.ParameterName);
adapter.Fill(tableToFill, (OracleRefCursor)parameters.Value);
}
}
}
}
finally
{
command.Dispose();
// clean up a dangling automaticly opened connection if needed.
if(!(base.KeepConnectionOpen || base.IsTransactionInProgress))
{
base.CloseConnection();
}
}

return true;
}

FB

--

 

[Ram]

David,

I ran just the SQL only. Not as a ref cursor. So I can not say for sure
if the same slow down occurs if I have another SP in Oracle itself
trying to access this REFCURSOR and use it. I will try to do that and
see what the results will look like.

Thanks,
Ram

 

[Ram]

Frans,

Couple of quick clarifications. I am using MS provider for oracle and
ODP.NET.

Thanks,
Ram

 

[Frans Bouma [C# MVP]]

Frans,

Couple of quick clarifications. I am using MS provider for oracle and
ODP.NET.

those are the same, so either ODP.NET or the ms provider. My
example was for odp.net.

For the ms provider, please see the example in the OracleDataAdapter
overview in the MSDN documentation.

FB

--