improve .NET stored procedure performance
665144Nov 4 2008 — edited Dec 15 2008Hi,
I have the following C# code in my .NET stored procedure:
int tempNumber = 0;
OracleConnection OracleConn = new OracleConnection();
// The StatementCacheSize parameter in my registry for ODP.NET is set to 10.
// Maybe it is not needed to set "Statement Cache Size" here to ensure statement caching is enabled?
OracleConn.ConnectionString = "context connection=true; Statement Cache Size=10";
OracleConn.Open();
OracleCommand OracleCmd = new OracleCommand();
OracleCmd.Connection = OracleConn;
OracleCmd.CommandText = "select distinct g3e_fid, " + ColumnName + " from " + TableName + " order by g3e_fid";
OracleDataReader OracleReader = OracleCmd.ExecuteReader();
OracleReader.FetchSize = OracleCmd.RowSize * 1000;
while (OracleReader.Read())
{
if (OracleReader.IsDBNull(1))
tempNumber = 10000;
else
tempNumber = Convert.ToInt32(OracleReader.GetValue(1)) + 10000;
OracleCmd.AddToStatementCache = true; // Maybe this is not needed because statement caching is enabled?
OracleCmd.CommandText = "update " + TableName + " set " + ColumnName + " = :num where g3e_fid = :fid";
OracleCmd.Parameters.Add(":num", OracleDbType.Int32, tempNumber, ParameterDirection.Input);
OracleCmd.Parameters.Add(":fid", OracleDbType.Int32, Convert.ToInt32(OracleReader.GetValue(0)), ParameterDirection.Input);
OracleCmd.ExecuteNonQuery();
OracleCmd.Parameters.Clear();
}
When I ran it against a table that has about 46000 rows, it took about 50 seconds.
I also wrote a PL/SQL procedure that does the same thing. I used bulk collect with limit clause. I ran it against the same table and it only took about 3 seconds.
I know PL/SQL has performance advantage for data intensive operations. Are there some ways to improve the performance of my .NET SP?
Thank you so much!