Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
Click Here
|
Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
|
SP_REPORTS and OPTIMIZATION |
Author |
Message |
|
Posted : 2007-10-27 00:26:09 |
Is it possible to using optimization with an sp_report
Here is my sp_report,, if I just run this, it takes about 14 hours..which is unacceptable.
How can I use my optimized data?
(Query below)
sp_report PayrollItemDetail show RefNumber_Title, Date_Title, SourceName_Title, PayrollItem_Title, TxnType_Title, WageBase_Title, Amount_Title, Text, Blank, TxnType, Date, RefNumber, SourceName, PayrollItem, WageBase, Amount parameters DateMacro = 'All', IncludeAccounts = 'InUse' |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-10-29 08:22:16 |
The QODBC Optimizer works for tables only. Sp_report uses the QuickBooks Reporting engine directly. Your report takes a long time because of the use of: parameters DateMacro = 'All'
All the predefined DateMacro options available to you are: |All|Today|ThisWeek|ThisWeekToDate|ThisMonth|ThisMonthToDate|ThisQuarter|ThisQuarterToDate |ThisYear|ThisYearToDate|Yesterday|LastWeek|LastWeekToDate|LastMonth|LastMonthToDate|LastQuarter |LastQuarterToDate|LastYear|LastYearToDate|NextWeek|NextFourWeeks|NextMonth|NextQuarter|NextYear|
Try using ThisMonth or ThisWeek. |
|
|
|
|
Posted : 2007-10-30 04:52:58 |
The DateMacro is definately not the issue, even if I make it 'ThisWeek' it takes hours...
very frustrating. it all worked fine in version 6, but version 8 is painful, what's changed? (ive also updated to enterprise 8)
im not hoping for much, but any help is appreciated |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-10-30 07:33:36 |
Try copying the company file to your workstation or another location on your server and changing the company file to single user mode. I've seen other Enterprise 2008 complain that reports are slow when their company files are in multi-user mode. |
|
|
|
|
Posted : 2008-02-07 05:36:05 |
If sp_report is going to run so slow on our Enterprise 2008, then we need an alternate way of running the TxnListByDate report. We currently grab info using the DateFrom macro to get all data since Jan. 1, 2007, and that takes about 13 hours with the new Enterprise edition, where it used to take about 2 hours with 2005.
Could you supply an equivalent SQL query that would be able to take advantage of the optimizer? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-02-07 08:09:25 |
The statement is:
SELECT * FROM Transaction where TxnDate >= {d'2007-01-01'}
If you're retieving historican data you can use the optimized data directly like this:
SELECT * FROM Transaction NOSYNC where TxnDate >= {d'2007-01-01'} and TxnDate < {d'2008-01-01'}
|
|
|
|
|