Databases
StackOverflow2013
Postsdbo
POODP.Net - OracleDataReader.Read very slow
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POODP.Net - OracleDataReader.Read very slow
    primarykey
    data
    text
    <p>I'm having a lot of trouble with the OracleDataReader in ODP.Net. Basically, I have a parameterized query that takes anywhere from 1-5 seconds to run (returning around 450 records) and then takes 60-90 seconds to loop over (with no code even running in the loop, literally iterating over the recordset and doing nothing).</p> <p>When I run it from Aqua Data Studio it takes 1-5 seconds. When I run it from .Net it takes 1-5 seconds for cmd.ExecuteReader() to return. When I loop over the 450 records with OracleDataReader.Read it takes 60-90 seconds to finish.</p> <p>I even took out all of the code in the loop and just had a blank "While dr.Read" and it still took 60 to 90 seconds to loop over those 450 records (I used a Stopwatch to get the time for the cmd.ExecuteReader and then around the empty dr.Read loop).</p> <p>I have tried setting the FetchSize, it didn't help (and, it's only 450 records in my test case). I have tried turning auto tuning off with the connection string, it degraded performance even more.</p> <p>Why is the OracleDataReader.Read taking so long when it's a small amount of data being returned (and other tools return the same data for the same query in a fraction of the time)? </p> <pre><code> Using conn As New Oracle.DataAccess.Client.OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings("oracle_dss").ConnectionString) conn.Open() Using cmd As OracleCommand = conn.CreateCommand cmd.BindByName = True cmd.CommandText = "" ' removed SQL to make this more readable ' Month end Dim paramMonthEndDate As OracleParameter = cmd.CreateParameter paramMonthEndDate.ParameterName = ":month_end_date" paramMonthEndDate.DbType = DbType.Date paramMonthEndDate.Value = monthEnd cmd.Parameters.Add(paramMonthEndDate) Dim sw As New System.Diagnostics.Stopwatch sw.Start() cmd.FetchSize = 1000 Dim dr As OracleDataReader = cmd.ExecuteReader dr.FetchSize = dr.RowSize * 1000 sw.Stop() Me.Log(String.Format("Month End Query: {0}s", sw.ElapsedMilliseconds / 1000)) sw.Reset() sw.Start() While dr.Read End While sw.Stop() Me.Log(String.Format("Month End Query through recordset: {0}s", sw.ElapsedMilliseconds / 1000)) dr.Close() End Using conn.Close() End Using </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USb.pell
    1. USb.pell
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COCould you show your code.. perhaps you are doing something with opening and closing the connections basically too many round trips but can't tell without seeing any existing code. Also have you looked to see if the Query is `Hitting the Indexes` if any are on the table that you are querying
      singulars
      1. POODP.Net - OracleDataReader.Read very slow
      1. USMethodMan
    2. COLet me get my code and I'll update the question. It is indexed.. and it runs to completion in Aqua Data Studio (3rd party query utility) with all results returned in a couple of seconds. I would think if the indexes weren't working that would also be an equivalent slow.
      singulars
      1. POODP.Net - OracleDataReader.Read very slow
      1. USb.pell
    3. COAre you certain about the server response time? There may also be an issue with the oracle configuration.
      singulars
      1. POODP.Net - OracleDataReader.Read very slow
      1. USSemih Yagcioglu
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload