可能是我错了，但实际上你获取数据此行中：虽然dr.Read ，而不是当你正在执行的读者。因此，这可以解释，为什么即使没有什么也不做， dr.Read 采取一切你的时间。

我想尝试你的命令，切换到

1）。运行纯SQL（不带参数）

2）。运行使用常规（非绑定变量）参数

3）。如果可能的话将SQL code存储过程

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).

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.

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).

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.

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)?

    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

May be I am wrong, but you actually fetch the data in this row: While dr.Read, and not when you are executing the reader. So this can explain why even without doing nothing, dr.Read take all your time.

I'd try to change your command to

1). Run plain sql (without parameters)

2). Run using regular (not binding variable) parameter

3). Move the sql code to Stored Procedure if possible