oracle 为什么 OracleDataAdapter.Fill() 很慢？

Why is OracleDataAdapter.Fill() Very Slow?

提问by John Gietzen

I am using a pretty complex query to retrieve some data out of one of our billing databases.

我正在使用一个非常复杂的查询从我们的一个计费数据库中检索一些数据。

I'm running in to an issue where the query seems to complete fairly quickly when executed with SQL Developer, but does not seem to ever finish when using the OracleDataAdapter.Fill()method.

我遇到了一个问题，当使用 SQL Developer 执行时，查询似乎很快完成，但在使用该OracleDataAdapter.Fill()方法时似乎永远不会完成。

I'm only trying to read about 1000 rows, and the query completes in SQL Developer in about 20 seconds.

我只尝试读取大约 1000 行，查询在 SQL Developer 中完成大约 20 秒。

What could be causing such drastic differences in performance? I have tons of other queries that run quickly using the same function.

是什么导致了如此巨大的性能差异？我有大量其他查询可以使用相同的功能快速运行。

---

---

Here is the code I'm using to execute the query:

这是我用来执行查询的代码：

using Oracle.DataAccess.Client;

...

public DataTable ExecuteExternalQuery(string connectionString, string providerName, string queryText)
{
    DbConnection connection = null;
    DbCommand selectCommand = null;
    DbDataAdapter adapter = null;

    switch (providerName)
    {
        case "System.Data.OracleClient":
        case "Oracle.DataAccess.Client":
            connection = new OracleConnection(connectionString);
            selectCommand = connection.CreateCommand();
            adapter = new OracleDataAdapter((OracleCommand)selectCommand);
            break;
        ...
    }

    DataTable table = null;
    try
    {
        connection.Open();

        selectCommand.CommandText = queryText;
        selectCommand.CommandTimeout = 300000;
        selectCommand.CommandType = CommandType.Text;

        table = new DataTable("result");
        table.Locale = CultureInfo.CurrentCulture;
        adapter.Fill(table);
    }
    finally
    {
        adapter.Dispose();

        if (connection.State != ConnectionState.Closed)
        {
            connection.Close();
        }
    }

    return table;
}

And here is the general outline of the SQL I'm using:

这是我正在使用的 SQL 的大纲：

with
  trouble_calls as
  (
    select
      work_order_number,
      account_number,
      date_entered
    from
      work_orders
    where
      date_entered >= sysdate - (15 + 31)  -- Use the index to limit the number of rows scanned
     and
      wo_status not in ('Cancelled')
     and
      wo_type = 'Trouble Call'
  )
select
  account_number,
  work_order_number,
  date_entered
from
  trouble_calls wo
where
  wo.icoms_date >= sysdate - 15
 and
  (
    select
      count(*)
    from
      trouble_calls repeat
    where
      wo.account_number = repeat.account_number
     and
      wo.work_order_number <> repeat.work_order_number
     and
      wo.date_entered - repeat.date_entered between 0 and 30
  ) >= 1

回答by Will Marcouiller

There are known performance differences between using the Microsoft Data Provider for Oracle and the native Oracle Data Provider.

使用 Microsoft Data Provider for Oracle 和本机 Oracle Data Provider 之间存在已知的性能差异。

Have you tried both?

你两个都试过吗？

What are you trying to achieve with this query? Forget about technical stuff, just the objective of it all. Perhaps is there a tune possible for your query.

你想用这个查询实现什么？忘记技术性的东西，这只是它的目标。也许您的查询可能有曲调。

Have you tried with a profiler to see where it gets stuck?

您是否尝试过使用分析器来查看它卡住的地方？

回答by Angelo

I think the culture and the date being returned by your Oracle query are different and that is where the application is taking a lot of time to parse.

我认为您的 Oracle 查询返回的文化和日期是不同的，这就是应用程序需要花费大量时间来解析的地方。

回答by Taras Kozubski

This code helped me, try it:

这段代码帮助了我，试试看：

using (OracleConnection conn = new OracleConnection())
{
     OracleCommand comm = new OracleCommand();
     comm.Connection = conn;
     comm.FetchSize = comm.FetchSize * 16;
     comm.CommandText = "select * from some_table";

     try
     {
          conn.Open();
          OracleDataAdapter adap = new OracleDataAdapter(comm);
          System.Data.DataTable dt = new System.Data.DataTable();
          adap.Fill(dt);
     }
     finally
     {
          conn.Close();
     }
}

The trik is in line (try values from 8 to 64 to find the best for your case):

技巧是一致的（尝试从 8 到 64 的值以找到最适合您的情况）：

comm.FetchSize = comm.FetchSize * 16;

UPDATE:

更新：

Here is an improved code:

这是一个改进的代码：

OracleConnection myConnection = new OracleConnection(myConnectionString);
OracleCommand myCommand = new OracleCommand(mySelectQuery, myConnection);
myConnection.Open();
using (OracleDataReader reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
{
    // here goes the trick
    // lets get 1000 rows on each round trip
    reader.FetchSize = reader.RowSize * 1000;

    while (reader.Read())
    {
        // reads the records normally
    }
}// close and dispose stuff here

From here

从这里