Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

improve .NET stored procedure performance

665144Nov 4 2008 — edited Dec 15 2008
Hi,
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!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 12 2009
Added on Nov 4 2008
12 comments
5,354 views