ODP.NET

// 64-bit Oracle 11.2.0.3 on Windows 7 Professional
// 64-bit ODAC download and install from http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html
//
// Any help or comments appreciated.

Fairly simple C# code - fetch a record, for this record fetch child records, process and insert results into a table.

When using small sets of data, for example, 8,000 records - records are processed at the rate of 1,000/minute.

With increasingly larger sets of data, say 1 million, - records are processed at the rate of 5/minute.

Is there anything specific to the OracleDataReader class that is not implemented?

// Same experience with the 32 bit Oracle stack (database + ODAC) before switching to 64 bit.
// No difference in behavior.

The reader is closed and disposed of with each iteration of the record and child records.

Here is the C# code:

using System;
using System.Data;
using System.Collections.Generic;
using System.Diagnostics;
using Oracle.DataAccess.Client;
using Eni.ValueNavigator.DeclineFitting;
using Eni.ValueNavigator.DeclineFitting.Data;

namespace ConsoleApplication1
{
class Program
{
public static int StepsCount = 0;
public static int StepsCum = 0;

// Fit Settings
public static string fetchFitSettings = " SELECT /*+ FIRST_ROWS*/ Entity_Id " +
" FROM FITSETTINGS WHERE state ='TX' ";

// StepsToFit
public static string fetchStepsToFit = " SELECT /*+ FIRST_ROWS*/ Entity_Id, TO_CHAR(ProductionDate,'YYYY/MM/DD') as ProductionDate, " +
" DaysAvg, DaysCum, DaysProd, Hours, Volume, 500 as Rate,SparseIndex " +
" FROM EUR_STAGING WHERE entity_id = :EntityId" +
" ORDER BY ProductionDate Desc";

// Results
public static string InsertResults = " INSERT INTO LOCAL_RESULTS (ENTITY_ID,STARTDATE,ENDDATE,STEPS,EUR_BBL,FITTIMEMS, LOADSTEPSMS) " +
" VALUES (:EntityID, :StartDate, :EndDate, :StepsCount,:segmentVolume,:FitTime,:StpsLdTime ) ";


public static string connectStr = "User Id=woutuser;Password=woutuser;Data Source=EUR;Pooling=false;Statement Cache Size=1";
public static OracleConnection conn = new OracleConnection(connectStr);
public static Stopwatch stopwatch = new Stopwatch();

// The main entry point for the application.
static void Main(string[] args)
{
DateTime StartTime = DateTime.Now;
// Connect to Staging database
try
{
conn.Open();
}
catch (Exception e)
{
Console.WriteLine("Oracle Connection Error: {0}", e.Message);
}

Int32 EntityID = 0;
Int32 RowCount = 0;

FitInputs fitInputs = new FitInputs();
var steps = new List<FitDataStep>();

// Create the OracleCommand and fetch FitSettings
OracleCommand FitSetCmd = new OracleCommand(fetchFitSettings, conn);
OracleDataReader FitSetRdr = FitSetCmd.ExecuteReader();


Console.WriteLine("Row " + "Entity ID " + " StartDate " + " EndDate " + " Steps " + "EUR (bbl) " + " Call(ms) " + " Load(ms)" );
try
{
while (FitSetRdr.Read())
{
{
EntityID = (int)FitSetRdr.GetOracleDecimal(0);
}
double StpsLdMs = 0L;
FillInputSettings(ref fitInputs);
stopwatch.Reset();
stopwatch.Start();
fitInputs.StepsToFit = FillDataSteps(EntityID);
stopwatch.Stop();
TimeSpan StpsLoadTime = stopwatch.Elapsed;
StpsLdMs = StpsLoadTime.TotalMilliseconds;

var result = new DeclineFittingResult();
double runTimeMs = 0L;
try
{
stopwatch.Reset();
stopwatch.Start();
result = FitCalculator.StartCalculation(fitInputs);
stopwatch.Stop();
TimeSpan runTime = stopwatch.Elapsed;
runTimeMs = runTime.TotalMilliseconds;
}
catch (Exception ex)
{
Console.WriteLine("Exception :" + ex.ToString());
}

var segmentVolume = 0.0;
var declineRate = 0.0;
// EUR calculation based on exponential decline equation (a different calculation is required for harmonic and hyperbolic segments)
if (result.Segments.Count > 0)
{
declineRate = result.Segments[0].InitialSlope / 365.2425; // convert to #/d from #/yr
segmentVolume = (result.Segments[0].InitialRate - result.Segments[0].FinalRate) / declineRate;
Console.WriteLine(
(RowCount.ToString()) + " " +
(EntityID.ToString()) + " " +
(result.Segments[0].StartDate.ToString("d")) + " " +
(result.Segments[0].EndDate.ToString("d")) + " " +
(StepsCount.ToString()) + " " +
(segmentVolume.ToString("e4")) + " " +
(runTimeMs.ToString()) + " " + StpsLdMs.ToString()
);

// Insert into results table
OracleCommand InsertResultsCmd = new OracleCommand(InsertResults, conn);
InsertResultsCmd.Parameters.Add(new OracleParameter("EntityID", OracleDbType.Int32, EntityID, ParameterDirection.Input));
InsertResultsCmd.Parameters.Add(new OracleParameter("StartDate", OracleDbType.Varchar2, result.Segments[0].StartDate, ParameterDirection.Input));
InsertResultsCmd.Parameters.Add(new OracleParameter("EndDate", OracleDbType.Varchar2, result.Segments[0].EndDate, ParameterDirection.Input));
InsertResultsCmd.Parameters.Add(new OracleParameter("StepsCount", OracleDbType.Int32, StepsCount, ParameterDirection.Input));
InsertResultsCmd.Parameters.Add(new OracleParameter("segmentVolume", OracleDbType.Decimal, segmentVolume, ParameterDirection.Input));
InsertResultsCmd.Parameters.Add(new OracleParameter("runtimeMs", OracleDbType.Decimal, runTimeMs, ParameterDirection.Input));
InsertResultsCmd.Parameters.Add(new OracleParameter("StpsLdMs", OracleDbType.Decimal, StpsLdMs, ParameterDirection.Input));
// Execute the command
InsertResultsCmd.ExecuteNonQuery();
//Console.WriteLine("Insert Completed Successfully");


}
else
{
Console.WriteLine(
(RowCount.ToString()) + " " +
(EntityID.ToString()) + " " +
(StepsCount.ToString()) + " " +
(segmentVolume.ToString("e4"))
);
Console.WriteLine(" ");
}

//var historyVolume = steps.Sum(x => x.Volume);
//Console.WriteLine("\r\nEUR (bbl): " + segmentVolume + historyVolume);
//Console.WriteLine("\r\nEUR (bbl): " + segmentVolume);
RowCount++;
}
// No more eligible records in FitSettings
FitSetRdr.Close();
FitSetCmd.Dispose();
Console.WriteLine("Fit Data Steps Command and Reader Disposed.....");
}
catch (Exception e)
{
Console.WriteLine("Error - GetOraType Error: {0}", e.Message);
//Console.WriteLine("Insert Completed Successfully");

}
Console.WriteLine();
Console.WriteLine("Started "+ Convert.ToString(StartTime) + " End Time " + Convert.ToString(DateTime.Now));
Console.WriteLine("Total Entities " + Convert.ToString(RowCount) + " Total Steps " + Convert.ToString(StepsCum));
Console.WriteLine("Press ENTER to continue.");
Console.ReadLine();
}

private static void FillInputSettings( ref FitInputs fitInputs)
{
// GetFitSettings
fitInputs.Product = FittableProducts.Oil;
fitInputs.PrimaryProduct = FittableProducts.Oil;
fitInputs.CurrentMonth =DateTime.Parse( "1/1/2013" ) ;
fitInputs.FitSettings.StartDate = null;
fitInputs.FitSettings.EndDate = null;
fitInputs.FitSettings.FitAgainstEntireHistoryRange = false;
fitInputs.FitSettings.MaxLife = 2100;
fitInputs.FitSettings.MaxNonProducingMonthsBeforeShutIn = -1;
fitInputs.FitSettings.SlopeType = SlopeType.Nominal;

fitInputs.ProductFitSettings.FitExponential = true;
fitInputs.ProductFitSettings.FitHarmonic = true;
fitInputs.ProductFitSettings.FitHyperbolic = true;
fitInputs.ProductFitSettings.MaxN = 200;
fitInputs.ProductFitSettings.MinN = -3;
fitInputs.ProductFitSettings.DefaultSlope = 0.15;
fitInputs.ProductFitSettings.DefaultExponent = 0.15;
fitInputs.ProductFitSettings.DefaultMaxRate = 156;
fitInputs.ProductFitSettings.TechnicalTerminationRate = 1;

}

private static List<FitDataStep> FillDataSteps(int EntityId)
{
List<FitDataStep> result = new List<FitDataStep>();
OracleCommand StpsToFitCmd = new OracleCommand(fetchStepsToFit, conn);
StpsToFitCmd.Parameters.Add(new OracleParameter(":EntityId", OracleDbType.Int32));
StpsToFitCmd.Parameters[":EntityId"].Value = EntityId;
StepsCount = 0;
try
{
OracleDataReader StpsToFitRdr = StpsToFitCmd.ExecuteReader();
StpsToFitRdr.FetchSize = StpsToFitRdr.RowSize * 400;
stopwatch.Reset();
stopwatch.Start();
while (StpsToFitRdr.Read())
{
StepsCount++;

// if (StepsCount > 11)
// break;

FitDataStep tmpStep = new FitDataStep();

tmpStep.Date = DateTime.Parse(StpsToFitRdr.GetString(1));
tmpStep.DaysAvg = (double)StpsToFitRdr.GetDecimal(2);
tmpStep.DaysCum = (double)StpsToFitRdr.GetDecimal(3);
tmpStep.DaysProd = (double)StpsToFitRdr.GetDecimal(4);
tmpStep.Hours = (double)StpsToFitRdr.GetDecimal(5);
tmpStep.Volume = (double)StpsToFitRdr.GetDecimal(6);
tmpStep.Rate = (double)StpsToFitRdr.GetDecimal(7);
tmpStep.SparseIndex = (int)StpsToFitRdr.GetOracleDecimal(8);

result.Add(tmpStep);
};
StepsCum = StepsCum + StepsCount;
StpsToFitRdr.Close();
stopwatch.Stop();
TimeSpan StpsLoadTime = stopwatch.Elapsed;
// Console.WriteLine("Data Steps Load Time: {0}", StpsLoadTime.TotalMilliseconds.ToString());
}
catch (Exception e)
{
Console.WriteLine("GetFitDataSteps - GetOraType Error: {0}", e.Message);
Console.ReadLine();
}
return result;
}
}
}


// outout of MOS 578855.1

21:40:01 SQL> /

USERNAME NAME VALUE
-------------------- ------------------------------ ----------------
WOUTUSER:21,209 opened cursors cumulative 1,075,553
WOUTUSER:21,209 opened cursors current 3
WOUTUSER:21,209 session pga memory 971,104
WOUTUSER:21,209 session pga memory max 5,558,624

21:40:02 SQL> /

USERNAME NAME VALUE
-------------------- ------------------------------ ----------------
WOUTUSER:21,209 opened cursors cumulative 1,075,553
WOUTUSER:21,209 opened cursors current 3
WOUTUSER:21,209 session pga memory 971,104
WOUTUSER:21,209 session pga memory max 5,558,624

21:40:03 SQL> /

USERNAME NAME VALUE
-------------------- ------------------------------ ----------------
WOUTUSER:21,209 opened cursors cumulative 1,075,553
WOUTUSER:21,209 opened cursors current 3
WOUTUSER:21,209 session pga memory 971,104
WOUTUSER:21,209 session pga memory max 5,558,624

21:40:03 SQL> /

USERNAME NAME VALUE
-------------------- ------------------------------ ----------------
WOUTUSER:21,209 opened cursors cumulative 1,075,555
WOUTUSER:21,209 opened cursors current 3
WOUTUSER:21,209 session pga memory 971,104
WOUTUSER:21,209 session pga memory max 5,558,624

21:40:06 SQL> /

USERNAME NAME VALUE
-------------------- ------------------------------ ----------------
WOUTUSER:21,209 opened cursors cumulative 1,075,563
WOUTUSER:21,209 opened cursors current 3
WOUTUSER:21,209 session pga memory 971,104
WOUTUSER:21,209 session pga memory max 5,558,624

Edited by: 943139 on Jul 21, 2012 7:42 PM