我只是想了解1000行，在查询完成在SQL Developer中在大约20秒。

这可能是导致性能如此激烈的区别在哪里？我有吨的奔驰使用相同功能的其它查询。

下面是code我使用执行查询：

 使用Oracle.DataAccess.Client;

...

公开数据表ExecuteExternalQuery（字符串的connectionString，字符串的providerName，串QUERYTEXT）
{
    的DbConnection连接= NULL;
    的DbCommand的SelectCommand = NULL;
    DbDataAdapter的适配器= NULL;

    开关（的providerName）
    {
        案System.Data.OracleClient的：
        案Oracle.DataAccess.Client：
            连接=新的OracleConnection（的connectionString）;
            的SelectCommand = connection.CreateCommand（）;
            适配器=新OracleDataAdapter（（的OracleCommand）的SelectCommand）;
            打破;
        ...
    }

    数据表表= NULL;
    尝试
    {
        connection.Open（）;

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

        表=新的DataTable（结果）;
        table.Locale = CultureInfo.CurrentCulture;
        adapter.Fill（表）;
    }
    最后
    {
        adapter.Dispose（）;

        如果（connection.State！= ConnectionState.Closed）
        {
            的Connection.close（）;
        }
    }

    返回表;
}
 

这里是我使用的SQL的大致轮廓：

 与
  trouble_calls作为
  （
    选择
      work_order_number，
      帐号，
      date_entered
    从
      work_orders
    哪里
      date_entered＆GT = SYSDATE  - （15 + 31） - 使用索引来限制扫描行数
     和
      wo_status不是（'取消'）
     和
      wo_type =投诉电话
  ）
选择
  帐号，
  work_order_number，
  date_entered
从
  trouble_callsを
哪里
  wo.icoms_date＆GT; = SYSDATE  -  15
 和
  （
    选择
      计数（*）
    从
      trouble_calls重复
    哪里
      wo.account_number = repeat.account_number
     和
      wo.work_order_number＆LT;＆GT; repeat.work_order_number
     和
      wo.d​​ate_entered  -  repeat.date_entered 0和30之间
  ）＆GT; = 1
 

解决方案

有已知的性能差异使用Microsoft数据提供程序的Oracle和原生的Oracle数据提供的。

您是否尝试过两者兼而有之？

什么是你想达到这个查询？忘记技术的东西，这一切仅仅是客观的。也许是有可能的调整您的查询。

你试过了探查，看看它卡住？

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.

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

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:

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

There are known performance differences between using the Microsoft Data Provider for Oracle and the native 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?