title: Firebird on C#: FbCommand.ExecuteReader() performance
body: <p>I'm using C# (sharpdevelop) to access a firebird 2.5 server. This works really well. However, I have run into a performance problem.
  I have two tables, Members and Transactions which store who bought what when. To see who has not been active in a while I run the following compound query:</p>
  
  <pre><code>SELECT * FROM members M 
  WHERE exists (select 1 from transactions WHERE taDateTime &gt;= '30.06.2013' and Memberid = M.ID)
  </code></pre>
  
  <p>So, the Members-table has a primary index on field ID and the table transactions has indeces on both taDateTime and MemberID.</p>
  
  <pre><code>myConnection = new FbConnection(myConnectionString);
  Connection.Open();
  
  // property of a helper class, uses myConnection
  // Then when I press the Search button:
  // create the sql command, because the cut-off date is passed as a dbParam
  IDbCommand aCmd = new FbCommand(sql, Connection);
  IDataReader aReader = aCmd.ExecuteReader();
  </code></pre>
  
  <p>I now have a few 10'000 entries in members and several 100'000 entries in transactions. If I run this query in flamerobin, it takes about 2.6 sec to run. That's not thrilling, but sufficient. But If I run this query in C#, I need 3 minutes just to return from the call
    FbCommand.ExecuteReader(sql).
  I've not yet even looped over the result set, this is purely the call to <code>ExecuteReader()</code>. What does that do which takes so long?</p>
  
  <p>I've tried several other things, sich as limiting the number of results by adding a <code>ROWS</code> or <code>FIRST</code> clause, both with no effect. So it's not the number of results which matters. 
  I've tried to split the call into 2 queries: First find the <code>DISTINCT(Transaction.MemberID)</code> and then find the Members which fit, but there are too many IDs to sensibly pass into the second query.
  I've even programmed a loop which first finds the MemberIDs and then runs a <code>"SELECT * FROM Members where ID = xx"</code> for every result and that was faster than the single statement. So what is it which makes the call to ExecuteReader delay so long?</p>
  
  <p>Please help, I'm really at a loss here.</p>
  
  <p>Kind regards,
  Hannes</p>