Stored Procedure in Oracle and IDataReader.Read() in VB.NET

Question

I have a Stored Procedure that seems to be very slow.

Executing it in Oracle SQL Developer;

SET TIMING ON;

DECLARE
  CUR_OUT UTILS.T_CURSOR;
  P_ARTTYID NUMBER;
  P_ORDERST VARCHAR2(200);
  P_DRUMNO VARCHAR2(200);
  P_SHIPPINGNO VARCHAR2(200);
  P_DELIVERYDATEFROM DATE;
BEGIN
  P_ARTTYID := 2;
  P_ORDERST := '3';
  P_DRUMNO := '611-480';
  P_SHIPPINGNO := NULL;
  P_DELIVERYDATEFROM := '2005-01-01';

  C_T_ORDER_GETOVERVIEW(
    CUR_OUT => CUR_OUT,
    P_ARTTYID => P_ARTTYID,
    P_ORDERST => P_ORDERST,
    P_DRUMNO => P_DRUMNO,
    P_SHIPPINGNO => P_SHIPPINGNO,
    P_DELIVERYDATEFROM => P_DELIVERYDATEFROM
  );

  --DBMS_OUTPUT.PUT_LINE('CUR_OUT = ' || CUR_OUT); -- Doesn´t work ;|
END;

Gives the "Statement output"

anonymous block completed
139ms elapsed

Now the problem is when I call it from my VB.NET application using Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader() that returns a System.Data.IDataReader and the following function to convert the IDataReader to a DataSet.

    Public Shared Function ConvertIDataReaderToDataSet(ByVal reader As IDataReader) As DataSet
        Dim schemaTable As DataTable = reader.GetSchemaTable()
        Dim dataTable As DataTable = New DataTable

        For intCounter As Integer = 0 To schemaTable.Rows.Count - 1
            Dim dataRow As DataRow = schemaTable.Rows(intCounter)
            Dim columnName As String = CType(dataRow("ColumnName"), String)
            Dim column As DataColumn = New DataColumn(columnName, CType(dataRow("DataType"), Type))
            dataTable.Columns.Add(column)
        Next

        Dim dataSet As DataSet = New DataSet
        dataSet.Tables.Add(dataTable)

        'dataSet.Load(reader, LoadOption.OverwriteChanges, dataTable) ' DEBUG
        While reader.Read()
            Dim dataRow As DataRow = dataTable.NewRow()
            For intCounter As Integer = 0 To reader.FieldCount - 1
                dataRow(intCounter) = reader.GetValue(intCounter)
            Next
            dataTable.Rows.Add(dataRow)
        End While

        Return dataSet
    End Function

Debugging and stepping through the function ends at the line for "While reader.Read()". Also tried another version using DataSet.Load() but with the same result.

Found this thread on MSDN where others with the same problem seems to have solved it by tuning their queries by adding indexes.

How can I continue investigating the issue when it seems like the procedure works (responds within ~100 - 200ms) and the IDataReader.Read() just ends (or continues in the background?)

Can I time the procedure in another (better) way?
Can there be any table or transaction locks involved?

All advices are highly appreciated :)

Justin Cave · Accepted Answer · 2011-09-21T14:30:49.707

Your test in SQL Developer is simply measuring the time required to open the cursor. Opening the cursor does not cause Oracle to actually execute the query-- that does not happen until you fetch data from the cursor and each time you fetch, Oracle will continue processing the query to get the next set of rows. Oracle does not, in general, need to execute the entire query at any point in time. To be a comparable test, your PL/SQL block would need to fetch all the data from the cursor. Something like

DECLARE
  CUR_OUT UTILS.T_CURSOR;
  P_ARTTYID NUMBER;
  P_ORDERST VARCHAR2(200);
  P_DRUMNO VARCHAR2(200);
  P_SHIPPINGNO VARCHAR2(200);
  P_DELIVERYDATEFROM DATE;
BEGIN
  P_ARTTYID := 2;
  P_ORDERST := '3';
  P_DRUMNO := '611-480';
  P_SHIPPINGNO := NULL;
  P_DELIVERYDATEFROM := '2005-01-01';

  C_T_ORDER_GETOVERVIEW(
    CUR_OUT => CUR_OUT,
    P_ARTTYID => P_ARTTYID,
    P_ORDERST => P_ORDERST,
    P_DRUMNO => P_DRUMNO,
    P_SHIPPINGNO => P_SHIPPINGNO,
    P_DELIVERYDATEFROM => P_DELIVERYDATEFROM
  );

  LOOP
    FETCH cur_out
     INTO <<list of variables to fetch data into>>;

    EXIT WHEN cur_out%notfound;
  END LOOP;

  --DBMS_OUTPUT.PUT_LINE('CUR_OUT = ' || CUR_OUT); -- Doesn´t work ;|
END;

Are you saying that in your .Net code, the reader.Read() line never returns? Or are you saying your code aborts at that point?

Ok, thanks. Thats quite a difference. How can I fetch data from the cursor using PL/SQL? The reader.Read() continues (for minutes) until it finally times out. — Stefan, Sep 21 '11 at 14:08
@Stefan - Updated with an example of fetching from the cursor. It assumes that you know the structure of the result set that you'll get. If your result set has 6 columns, for example, you'd need to fetch into 6 appropriately typed variables. — Justin Cave, Sep 21 '11 at 14:32
Thanks alot Justin. It turned out to be the query (stored procedure) itself. I created a new question regarding the query at http://stackoverflow.com/questions/7517018/check-if-parameter-is-null-within-where-clause — Stefan, Sep 22 '11 at 15:08

Stored Procedure in Oracle and IDataReader.Read() in VB.NET

1 Answers1