|
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_report with double outer join very slow in Crystal Reports |
| Author |
Message |
|
|
| Posted : 2006-04-17 21:38:35 |
Hi,
I'm using the newest versions of QODBC, Quickbooks Enterprise 6.0, and Crystal Reports XI.
I'm doing a report based on the Inventory Valuation Summary in quickbooks, but I also need to get each item's custom fields.
As a result, I'm doing a double outer join from the sp_report to both the ItemInventory and ItemInventoryAssembly tables. I can't use the OJ syntax in a command with an sp_report, so I'm using Crystal's visual linking. I'm doing a "not enforced" left outer join from the RowData field in the sp_report to the FullName field in the two other tables.
My sp_report command is:
sp_report InventoryValuationSummary show ItemDesc, QuantityOnHand, RowType, RowData, RowDataType parameters DateFrom = {?date_as_of}, DateTo = {?date_as_of}, ItemFilterType = 'InventoryAndAssembly'
This report takes about 20 minutes to run - is there any way to make it faster, or accomplish the same thing with a different technique? Also, this same report was much faster - more like 3 minutes - before I upgraded to the newest QODBC to fix an unrelated issue. |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-04-18 08:59:02 |
We can't do anything about making sp_report run faster other than for example upgrading from QuickBooks Premier to the QuickBooks Enterprise Edition. What you can do is make the two table lookups faster by adding the nosync tag after the table name.
In QODBC v6.00.00.155 the Table modifiers used in SQL statements (tags used following the table name) to override QODBC Optimizer configuration screen settings have been enhanced:
VERIFIED | VERIFY - Forces Full Resync with QuickBooks on the optimized table before Query starts
CALLDIRECT | UNOPTIMIZED - Passthru query directly to QuickBooks - use no optimizations
OPTIMIZE | OPTIMIZED | NOSYNC - Passthru query to optimized table directly for maximum speed
Examples:
select * from customer UNOPTIMIZED
select * from customer NOSYNC
select * from customer VERIFIED
|
|
|
|
|
|
| Posted : 2006-04-19 02:22:06 |
How would I do this in Crystal?
Am I right in thinking that I can not do an SQL-based outer join in Crystal Reports with an sp_report on the left?
TIA,
-Paul Prince |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-04-19 08:05:16 |
| No the sp_report runs as a total sql statement seqence however using Crystal Reports you can build sub-reports linked to a column. |
|
|
|
|