Troubleshooting - Performance issue QODBC Test Tool versus my C# app.

Problem Description:

I am running the same query on both returns, a big difference. The QODBC Test Tool app takes around 7 seconds, and my C# app takes 1:20 minutes.

Are there any settings I need to set to get the same results?

Solution:

Your C# app needs an STA (single-threaded-apartment) threading model. Multi-threading slows QODBC down as we need to re-establish a connection to QuickBooks every time the Thread ID changes.

Check your C# Express documentation or help files for thread management techniques.

See also the MSDN .NET Framework Developer Center:  http://msdn2.microsoft.com/en-us/library/system.stathreadattribute.aspx. 

Note: ADODB works when you loop through the resultant Recordset via Recordset.MoveNext(), an additional query is made through QODBC for each record. These other queries caused 50 minutes of runtime for a 7000 invoice query.

The first function below demonstrates the ADODB method that took ~1 hour. The second does the same thing with ODBC and takes less than a minute. 

[STAThread]
static void test1() {
    ADD. Connection con = New ADODB.Connection();
    con.Open("DSN=QuickBooks Data;OLE DB Services=-2", "", "", -1);
    string invoiceSQL =
                "SELECT CustomerRefFullName, RefNumber, TxnDate, BalanceRemaining, AppliedAmount, Memo " +
                "FROM Invoice " +
                "WHERE TxnDate>{d'2006-04-02'}";
        ADODB.Recordset invoiceResult = new ADODB.Recordset();
        invoiceResult.Open(invoiceSQL, con, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, 0);
        while(!invoiceResult.EOF) {
                Console.WriteLine("Invoice #" + invoiceResult.Fields["RefNumber"].Value.ToString());
                invoiceResult.MoveNext();
        }
        con.Close();
}


[STAThread]
static void test2() {
        OdbcConnection con = new OdbcConnection("DSN=QuickBooks Data");
        con.Open();
        OdbcDataAdapter dAdapter = new OdbcDataAdapter(
                "SELECT CustomerRefFullName, RefNumber, TxnDate, BalanceRemaining, AppliedAmount, Memo " +
                "FROM Invoice " +
                "WHERE TxnDate>{d'2006-04-02'}", con);
        DataTable result = new DataTable();
        adapter.Fill(result);
        DataTableReader reader = new DataTableReader(result);
        while(reader.Read()){
                Console.WriteLine("Invoice #: " + reader.GetString(1));
        }
        con.Close();
}