Strange slow down with imbricated DataReader (Oracle, c#)

  • Thread starter Thread starter Guy
  • Start date Start date
G

Guy

This is my first c# windows app.

I create a data reader on an Oracle table. Returns 552 rows. I iterate
thru them, goes like light speed.

Next, for each row returned, I need to fetch from another table to get
some other information. When I do that, my program takes 20s instead
of 1s. I even downgraded my inner SQL to become "select 1 from dual",
same performance hit!

Is there a problem with Oracle under c# using two DataReader at the
same time?

Here's the code:

con = new System.Data.OracleClient.OracleConnection();
con.ConnectionString = "user id=xx;data source=xx;password=xx";

public void Process(ProgressBar progBar)
{

OracleDataReader dr;
OracleCommand cmd;

dr = GetMainDR()
while (dr.Read())
{
// get some columns
...
cmd = new OracleCommand("select 1 from dual", con);
Decimal i = (Decimal) cmd.ExecuteScalar(); <----- slows down
everything
cmd.Dispose();
...
}

}

public OracleDataReader GetMainDR()
{
OracleDataReader dr;
OracleCommand cmd;


cmd = new OracleCommand("select * from TABLE_OF_552_ROWS", con); //
change
dr = cmd.ExecuteReader();

return dr;
}
 
I am surprised you don't get an exception, must be something about the
Oracle provider.
Normally, you can only have one open datareader on a given connection.
Trying to open a second one before closing the first, causes an exception.
 
Next, for each row returned, I need to fetch from another table to get
some other information. When I do that, my program takes 20s instead
of 1s. I even downgraded my inner SQL to become "select 1 from dual",
same performance hit!
Click to expand...

I would try Procedure with Relation, it should be lightening fast.

John
 
Back
Top