title: Why is OracleDataAdapter.Fill() Very Slow?
body: <p>I am using a pretty complex query to retrieve some data out of one of our billing databases.</p>
  
  <p>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 <code>OracleDataAdapter.Fill()</code> method.</p>
  
  <p>I'm only trying to read about 1000 rows, and the query completes in SQL Developer in about 20 seconds.</p>
  
  <p>What could be causing such drastic differences in performance?  I have tons of other queries that run quickly using the same function.</p>
  
  <hr>
  
  <p>Here is the code I'm using to execute the query:</p>
  
  <pre><code>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;
  }
  </code></pre>
  
  <p>And here is the general outline of the SQL I'm using:</p>
  
  <pre><code>with
    trouble_calls as
    (
      select
        work_order_number,
        account_number,
        date_entered
      from
        work_orders
      where
        date_entered &gt;= 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 &gt;= sysdate - 15
   and
    (
      select
        count(*)
      from
        trouble_calls repeat
      where
        wo.account_number = repeat.account_number
       and
        wo.work_order_number &lt;&gt; repeat.work_order_number
       and
        wo.date_entered - repeat.date_entered between 0 and 30
    ) &gt;= 1
  </code></pre>