FirebirdSql (C#)比在FlameRobin中执行脚本慢

Question

我在我的FirebirdSql项目中有.NET dll版本4.1，它连接到FireBirdSQLServerVersion2.1，我使用FlameRobin客户端(非常方便的工具)测试Sql脚本。

无论如何，我面临的问题是，我在FlameRobin中执行的每个脚本都非常快，但是在我的.NET项目中通过FirebirdSql运行相同的脚本需要花费更长的时间。也许对此没有简单的答案，有没有人知道为什么在我的C#项目中会这么慢地工作呢？

这是我在Flamerobin中执行的sql-脚本，执行起来需要一秒时间：。

SELECT t.S_ID, t.TR_SEQ, t.TR_DATE, t.TR_TIME, t.TR_TERM_SLA, r.DR_NAME, t.TR_DPT_NO, t.TR_EVENT, ev.ET_DESC, t.TR_DIRECTION, t.TR_TAG_CODE, m.MST_FIRST_NAME, m.MST_LAST_NAME, t.TR_TT_TYPENO, t.TR_MSTSQ, t.TR_REASON_CODE, t.TR_PROCESSED
from TRANSACK t left outer join MASTER m on t.TR_MSTSQ = m.MST_SQ, EVENT_TYPE ev, READER r
where ev.ET_TYPENO = t.TR_EVENT and r.T_ADDR = t.TR_TERM_SLA and 20140205 <= t.TR_DATE and 20140206 >= t.TR_DATE and (m.MST_LAST_NAME like '%' or m.MST_LAST_NAME is null) and (m.MST_FIRST_NAME like '%' or m.MST_FIRST_NAME is null) and t.TR_TAG_CODE like '%'
order by t.TR_DATE desc, t.TR_SEQ desc

--这是我的C#项目中的同一个脚本，执行只需10秒多一点：

_firebirdContext.OpenConnection();
List<Transaction> stuffFromTransactions = new List<Transaction>();

FbCommand readTransaction = new FbCommand("SELECT t.S_ID, t.TR_SEQ, t.TR_DATE, t.TR_TIME, t.TR_TERM_SLA, r.DR_NAME, t.TR_DPT_NO, t.TR_EVENT, ev.ET_DESC, " + 
    "t.TR_DIRECTION, t.TR_TAG_CODE, m.MST_FIRST_NAME, m.MST_LAST_NAME, t.TR_TT_TYPENO, t.TR_MSTSQ, t.TR_REASON_CODE, t.TR_PROCESSED " +
    "from TRANSACK t left outer join MASTER m on t.TR_MSTSQ = m.MST_SQ, EVENT_TYPE ev, READER r " + 
    "where ev.ET_TYPENO = t.TR_EVENT and r.T_ADDR = t.TR_TERM_SLA and 20140205 <= t.TR_DATE and 20140206 >= t.TR_DATE and (m.MST_LAST_NAME like '%' or m.MST_LAST_NAME is null) and (m.MST_FIRST_NAME like '%' or m.MST_FIRST_NAME is null) and t.TR_TAG_CODE like '%'" +         
    "order by t.TR_DATE desc, t.TR_SEQ desc", _firebirdContext.FbConnection);
FbDataReader transactionReader = readTransaction.ExecuteReader();
while (transactionReader.Read())
{
    Transaction transaction = new Transaction();
    if (!Convert.IsDBNull(transactionReader[0]))
    {
        transaction.S_ID = (int)transactionReader[0];
    }
    if (!Convert.IsDBNull(transactionReader[1]))
    {
        transaction.TR_SEQ = (int)transactionReader[1];
    }

    // ...
    // abbreviated
    // ...

    if (!Convert.IsDBNull(transactionReader[16]))
    {
        transaction.TR_PROCESSED = (Int16)transactionReader[16];
    }
    stuffFromTransactions.Add(transaction);
}
transactionReader.Close();
_firebirdContext.CloseConnection();
return stuffFromTransactions;

Answer 1

很可能您不是在获取FlameRobin中的所有记录，而只是查看网格中的第一个x。