How to increase data reading performance of returned cursor value ADO.NET with Oracle Reader

29 views Asked by At

Trying to get some datas from Oracle Database. İ am getting all datas successfully but it is quitely slow. Especially after i get datas as cursor , when i try to read all datas with loop approximately waiting 7-8 second. it slows down. The Question is How can I increase performance of this loop?

İ have added codes and waiting for your help!

P.S. I can't share DB codes unfortunatelly.

using (OracleCommand command = new OracleCommand(storedProcedureName, connection))
{
    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add("p_PieceIdList", OracleDbType.Varchar2).Value = pieceList.p_PieceIdList;
    command.Parameters.Add("p_producttype", OracleDbType.Varchar2).Value = pieceList.p_producttype;
    command.Parameters.Add("p_grade", OracleDbType.Varchar2).Value = pieceList.p_grade;
    command.Parameters.Add("p_sodescrid", OracleDbType.Varchar2).Value = pieceList.p_sodescrid;
    command.Parameters.Add("p_solinedescrid", OracleDbType.Varchar2).Value = pieceList.p_solinedescrid;
    command.Parameters.Add("p_packed", OracleDbType.Varchar2).Value = pieceList.p_packed;
    command.Parameters.Add("p_bundle", OracleDbType.Varchar2).Value = pieceList.p_bundle;
    command.Parameters.Add("p_ProductionFrom", OracleDbType.Date).Value = pieceList.p_ProductionFrom;
    command.Parameters.Add("p_ProductionTo", OracleDbType.Date).Value = pieceList.p_ProductionTo;
    command.Parameters.Add("p_outcursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

    command.ExecuteNonQuery();
  OracleRefCursor cursor = (OracleRefCursor)command.Parameters["p_outcursor"].Value; 

    using (OracleDataReader dataReader = cursor.GetDataReader())
    {
        if (dataReader.Read())
            smartPackDatas = new smartPackPieceListCursor()
            {
                PRODUCT_TYPE = dataReader.IsDBNull(dataReader.GetOrdinal("PRODUCT_TYPE")) ? "" : dataReader.GetString(dataReader.GetOrdinal("PRODUCT_TYPE")), 
                QUALITY_ID = dataReader.IsDBNull(dataReader.GetOrdinal("QUALITY_ID")) ? "" : dataReader.GetString(dataReader.GetOrdinal("QUALITY_ID")),
                PIECE_ID = dataReader.IsDBNull(dataReader.GetOrdinal("PIECE_ID")) ? "" : dataReader.GetString(dataReader.GetOrdinal("PIECE_ID")),
                PARENT_PIECE_ID = dataReader.IsDBNull(dataReader.GetOrdinal("PARENT_PIECE_ID")) ? "" : dataReader.GetString(dataReader.GetOrdinal("PARENT_PIECE_ID")),
                PIECE_NUM_ID = dataReader.IsDBNull(dataReader.GetOrdinal("PIECE_NUM_ID")) ? 0 : dataReader.GetInt32(dataReader.GetOrdinal("PIECE_NUM_ID")),
                WEIGHT = dataReader.IsDBNull(dataReader.GetOrdinal("WEIGHT")) ? 0 : dataReader.GetDouble(dataReader.GetOrdinal("WEIGHT")),
                THICK = dataReader.IsDBNull(dataReader.GetOrdinal("THICK")) ? 0 : dataReader.GetDouble(dataReader.GetOrdinal("THICK")),
                WIDTH = dataReader.IsDBNull(dataReader.GetOrdinal("WIDTH")) ? 0 : dataReader.GetDouble(dataReader.GetOrdinal("WIDTH")),
                LENGTH = dataReader.IsDBNull(dataReader.GetOrdinal("LENGTH")) ? 0 : dataReader.GetDouble(dataReader.GetOrdinal("LENGTH")),
                ACTUAL_WEIGHT = dataReader.IsDBNull(dataReader.GetOrdinal("ACTUAL_WEIGHT")) ? 0 : dataReader.GetDouble(dataReader.GetOrdinal("ACTUAL_WEIGHT")),
                ACTUAL_THICK = dataReader.IsDBNull(dataReader.GetOrdinal("ACTUAL_THICK")) ? 0 : dataReader.GetDouble(dataReader.GetOrdinal("ACTUAL_THICK")),
                ACTUAL_WIDTH = dataReader.IsDBNull(dataReader.GetOrdinal("ACTUAL_WIDTH")) ? 0 : dataReader.GetDouble(dataReader.GetOrdinal("ACTUAL_WIDTH")),
                ACTUAL_LENGTH = dataReader.IsDBNull(dataReader.GetOrdinal("ACTUAL_LENGTH")) ? 0 : dataReader.GetDouble(dataReader.GetOrdinal("ACTUAL_LENGTH")),
                BUNDLE = dataReader.IsDBNull(dataReader.GetOrdinal("BUNDLE")) ? "" : dataReader.GetString(dataReader.GetOrdinal("BUNDLE")),
                BUNDLE_ID = dataReader.IsDBNull(dataReader.GetOrdinal("BUNDLE_ID")) ? "" : dataReader.GetString(dataReader.GetOrdinal("BUNDLE_ID")),
                CUSTOMER_ORDERS = dataReader.IsDBNull(dataReader.GetOrdinal("CUSTOMER_ORDERS")) ? "" : dataReader.GetString(dataReader.GetOrdinal("CUSTOMER_ORDERS"))
            };
    }
0

There are 0 answers