Knowledgebase: QODBC
[QODBC-Desktop] Troubleshooting - Performance issue VB Demo versus my C# app.
Posted by Jack - QODBC Support on 04 January 2017 10:25 AM

Troubleshooting - Performance issue VB Demo versus my C# app.

Problem Description:

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

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

Solution:

Your C# app needs to use an STA (single-threaded-apartment) threading model. Multi-threading actually 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, but when you loop through the resultant recordset via Recordset.MoveNext(), an additional query is made through QODBC for each record.  These additional queries caused an additional 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() {
    ADODB.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();
        dAdapter.Fill(result);
        DataTableReader reader = new DataTableReader(result);
        while(reader.Read()){
                Console.WriteLine("Invoice #: " + reader.GetString(1));
        }
        con.Close();
}

 

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).