I am using crystal reports to run the following query:

sp_report TxnDetailByAccount show TxnType, Date, RefNumber, AccountFullName, AccountName, Account, Amount parameters AccountFilterType = 'OrdinaryIncome', DateMacro = 'All' where rowtype = 'DataRow'

It runs very very very slowly - The QOBDC status box sits on "Find First Record" for ages - I gave up waiting after 45 mins and killed it.

I appreciate the query is going to return a large amount data, but to run the report in Quickbooks takes only about 10 mins to run - how long should I expect it to take in Crystal? 

This query used to be much faster under QODBC v6 - it is only since I upgraded to v7 that has become so slow. 

Is there anything I can do to speed it up?

First of all the report request is sent to QuickBooks and it responds be generatating a hugh XML document that's more than 10 times larger than the actual data and then QODBC has to parse the XML document and remove all the tags etc. Depending on the resources of the network and workstation the query is run on, this can render the system to a complete crawl.

To speed things up limit the report to just this financial year data. In fact it might be quicker to extract data on a year by year basis if you have to have it all.

sp_report TxnDetailByAccount show TxnType, Date, RefNumber, AccountFullName, AccountName, Account, Amount parameters AccountFilterType = 'OrdinaryIncome', DateForm = {d'2007-01-01'}, DateTo= {d'2007-12-31'} where rowtype = 'DataRow'

Thanks for that - you're right, if I change query to

sp_report TxnDetailByAccount show TxnType, Date, RefNumber, AccountFullName, AccountName, Account, Amount parameters AccountFilterType = 'OrdinaryIncome', DateMacro = 'ThisYearToDate' where rowtype = 'DataRow'

then it runs quickly.

So, now, if I split it into seperate queries for each year, how do I combine the data from the seperate queries into a single set of records from which to build a report?

eg -

sp_report TxnDetailByAccount show TxnType, Date, RefNumber, AccountFullName, AccountName, Account, Amount parameters AccountFilterType = 'OrdinaryIncome', DateMacro = 'ThisYearToDate' where rowtype = 'DataRow'

sp_report TxnDetailByAccount show TxnType, Date, RefNumber, AccountFullName, AccountName, Account, Amount parameters AccountFilterType = 'OrdinaryIncome', DateMacro = 'LastYear' where rowtype = 'DataRow'

How do I combine the data from these 2 commands, to create a single set of data including all of last year and the current year?  I thought a UNION might do it, like this, but that's obviously not correct:

sp_report TxnDetailByAccount show TxnType, Date, RefNumber, AccountFullName, AccountName, Account, Amount parameters AccountFilterType = 'OrdinaryIncome', DateMacro = 'ThisYearToDate' where rowtype = 'DataRow'
UNION
sp_report TxnDetailByAccount show TxnType, Date, RefNumber, AccountFullName, AccountName, Account, Amount parameters AccountFilterType = 'OrdinaryIncome', DateMacro = 'LastYear' where rowtype = 'DataRow'

Any help would be appreciated

Thanks!

The choice is to either run each year as a unlinked subreport in Crystal Reports or simply state the years you are interested in:

sp_report TxnDetailByAccount show TxnType, Date, RefNumber, AccountFullName, AccountName, Account, Amount parameters AccountFilterType = 'OrdinaryIncome', DateForm = {d'2006-01-01'}, DateTo= {d'2007-12-31'} where rowtype = 'DataRow'