[sklett]

Just getting my hands dirty with the ODBC stuff. Started with a simple transaction query:

Code:
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.

Code:
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?
This is a cached copy. Click here to see the original post.

[sklett]

RE: JOIN = loooong execution time

Just for giggles I tried this:

Code:
string weirdSql = “SELECT t.transaction_id ” +
“FROM transactions AS t, transaction_lines AS tl ” +
“WHERE tl.item_id in ‘434’ AND t.transaction_id = tl.transaction_id AND t.transaction_type = ‘Sales Order’ AND (t.status = ‘Pending Fulfillment’ OR t.status = ‘Pending Billing/Partially Fulfilled’)”;
While this didn’t take “as” long, it still took too long (9+ seconds for 5 results)

[sklett]

RE: JOIN = loooong execution time

I’ve logged a case for this performance problem: 640808

[Author]

Posts