Just getting my hands dirty with the ODBC stuff. Started with a simple transaction query:
string sql = "SELECT t.create_date, t.transaction_id, t.transaction_type, t.status " + "FROM transactions AS t " + "WHERE t.transaction_type = 'Sales Order' AND (t.status = 'Pending Fulfillment' OR t.status = 'Pending Billing/Partially Fulfilled') " + "ORDER BY t.create_date DESC";
This executes pretty quick, about 1.5 seconds.I then wanted to try some joins to see how they would perform.string sql = "SELECT e.name, t.create_date, t.transaction_id, t.transaction_type, t.status " + "FROM transactions AS t " + "LEFT JOIN customers AS e ON e.customer_id = t.entity_id " + "WHERE t.transaction_type = 'Sales Order' AND (t.status = 'Pending Fulfillment' OR t.status = 'Pending Billing/Partially Fulfilled') " + "ORDER BY t.create_date DESC";
AHHH! 28+ seconds!! I tried over and over... same result.My first question: Is this typical of the ODBC access? JOINs being so slow they are prohibitive? It would be quicker for me to do a separate query.I purchased ODBC for integration purposes because getting the data we needed from WebServices was too slow. If this is typical JOIN performance it will actually be SLOWER than using Web Services.I'm sure this isn't the case and I'm missing something or something is broken somewhere... right?