Using Crystal Reports, I do a very simple self-join to get three levels of nested account numbers.  The report takes anywhere from 15 minutes to over an hour to run and I have only 132 rows in the Account table and the query returns only 34 rows. I'm the only user and the database is local.  I tried both inner and left outer joins.  Can anyone explain why this takes forever or what I can do to speed it up?

Thanks!

SELECT "Account"."AccountNumber", "Account"."Name", "GrandParentAccount"."AccountNumber", "GrandParentAccount"."Name", "ParentAccount"."AccountNumber", "ParentAccount"."Name"
FROM   "Account" "GrandParentAccount", "Account" "ParentAccount", "Account" "Account" 
WHERE  ("GrandParentAccount"."ListID"="ParentAccount"."ParentRefListID") AND ("ParentAccount"."ListID"="Account"."ParentRefListID") AND "GrandParentAccount"."AccountNumber"='6400'

I didn't get any results for your example as I only have one sublevel in my Accounts table, but this SELECT statement came up within seconds for me:

SELECT "Account"."AccountNumber", "Account"."Name", "ParentAccount"."AccountNumber", "ParentAccount"."Name"
FROM   "Account" "ParentAccount", "Account" "Account"
WHERE   ("ParentAccount"."ListID"="Account"."ParentRefListID")
AND "ParentAccount"."AccountNumber"='7200'

Thanks for the reply Tom.  I tried your two-level query and it returned in a few seconds.  Then I added the third level and it has been running for more than five minutes already.

If possible, can you add a couple third-level accounts and try my version of the query?  Why would this be a problem?  An inner join selecting only 34 records should be quick.

Thanks,

Rob

Well it would if we were talking to a database. QODBC actually sends XML Requests to QuickBooks which sends XML back to QODBC and we strip out all the XML headers and produce what looks like virtual tables. Most of our magic is limited to two left joins etc.

Ideally QODBC v7 should be able to send this to our backend SQLite engine without a problem and be fast. I will have to play with this further.

Tom,

If I install the latest available download of QODBC v7 does it include the "backend SQLite engine"?  Should I try this approach?

Thanks again,

Rob

Both QODBC v6 and v7 use a backend SQLite engine that we call the QODBC optimizer. Using QODBC v7 with a 2007 (new) serial number/CD Key, we have enabled pass-thru queries directly to our backend instead of trying to get it out of QuickBooks. We basically check for updates and changes for the tables and linked tables involved in the query and pass through your three table self join select statement directly to the SQLite database engine to perform the task directly.

But that's only if you have brought a CD Key for QODBC v7 and are not using your QODBC v6 CD Key.