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
|
|
Optimizer does not load all data. |
Author |
Message |
Rob |
|
Group | : Members |
Posts | : 23 |
Joined | : 2006-06-29 |
|
Profile |
|
Posted : 2006-06-29 10:21:38 |
Hi,
Note: I am not sure if the previous post went through. Please disregard this if it did.
Creating an application which gets all Invoices from company 1 and create a bill in company 2. Both companies in QBOE. Company 1 requires 3 tables using the following requests:
Select {fn CONVERT(InvoiceLine.TxnDate,SQL_CHAR)}, {fn CONVERT(InvoiceLine.DueDate,SQL_CHAR)}, InvoiceLine.RefNumber, InvoiceLine.InvoiceLineDesc, InvoiceLine.InvoiceLineAmount, CheckExpenseLine.ExpenseLineAccountRefFullName FROM InvoiceLine, CheckExpenseLine WHERE InvoiceLine.CustomerRefListID = '210' AND InvoiceLine.CustomerRefListID = CheckExpenseLine.ExpenseLineCustomerRefLIstID AND InvoiceLine.InvoiceLineServiceDate = CheckExpenseLine.TxnDate AND InvoiceLine.InvoiceLineAmount = CheckExpenseLine.ExpenseLineAmount and {fn TIMESTAMPDIFF(SQL_TSI_MONTH,InvoiceLine.TimeCreated,{fn NOW()})} = 2 Order by InvoiceLine.TimeCreated
A similar query for BillExpenseLine.
To make things faster I do an SP_OptimizeFullSync for each Table before I issue the select statements. Somehow after posting the bills, some records are not entered causing invoices and bills not to balance. I also noticed that When OptimizeFullSync is done for CheckExpenseLine, it only loads 2600+ out of the 3,500+ records.
I have tried to do an Unoptimized but got lesser records, tried the same select with your {OJ and got lesser records as well.
Please help.
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-06-29 11:44:25 |
Please download and install QODBC v6.00.00.176 (see How can I get the latest version of QODBC? ), then retry and advise if there's any difference in the number of rowsets returned doing each of these select statements:
Select * from CheckExpenseLine Select TxnID from CheckExpenseLine Select * from CheckExpenseLine UNOPTIMIZED Select TxnID from CheckExpenseLine UNOPTIMIZED |
|
|
|
Rob |
|
Group | : Members |
Posts | : 23 |
Joined | : 2006-06-29 |
|
Profile |
|
Posted : 2006-06-29 13:45:16 |
All 4 selects came out 2209/3000+ records. Only records from May, 05 thru now were received. When I tried to sort using Order by ---- an error comes out about the 1000 limitation. Only way I got the others is if I do the following:
SELECT * FROM CHECKEXPENSELINE UNoptimized where TxnDate < {d'2005-06-01'}
On the other hand, my application got more records. A little bit more accurate when UNOPTIMIZED in billexpense and checkexpense. I am running backwards... May, April and March and on. So far so good. I just hope everything can be optimized. Is there a way to load the first half into Optimizer and do an update to load the second half? Maybe a limiter in SP_Optimize.... |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-06-30 08:25:07 |
I suggest you reset your optimized tables, see How do I switch OFF or RESET the QODBC optimizer? and then resync your optimized CHECKEXPENSELINE table by running:-
sp_optimizefullsync CHECKEXPENSELINE
The QuickBooks Online Edition limits the number of rows returned by a query to 1,000, so you will need to break large queries into smaller query sets like this:
SELECT * FROM CHECKEXPENSELINE where TxnDate > {d'2003-06-30'} and TxnDate < {d'2004-07-01'} SELECT * FROM CHECKEXPENSELINE where TxnDate > {d'2004-06-30'} and TxnDate < {d'2005-07-01'} SELECT * FROM CHECKEXPENSELINE where TxnDate > {d'2005-06-30'} and TxnDate < {d'2006-07-01'}
Once the data is loaded in the optimized table, you can then do larger queries doing:
SELECT * FROM CHECKEXPENSELINE NOSYNC where TxnDate < {d'2005-06-01'}
|
|
|
|
Rob |
|
Group | : Members |
Posts | : 23 |
Joined | : 2006-06-29 |
|
Profile |
|
Posted : 2006-07-01 01:28:17 |
Records start from 2005-01-03.
Select * From CheckExpenseLine where TxnDate > {d'2005-01-01'} and Txndate < {d'2005-06-01'}
Returns 13 records
Unoptimized returns 500+ records
I turned on Status for Optimizer and Driver and noticed something.
When Optimized (first select), Optimizer shows 2187 records, Driver shows 2187 of 2187 records, then it pauses then it tries the do an xml parse again and total records goes to 4374 and records read jumps to 2188 then goes back to 2187. All status closes.
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-07-01 09:15:41 |
It looks like you will need to limit your queries to less than 1,000 records returned for each query using the UNOPTIMIZED tag. |
|
|
|
Rob |
|
Group | : Members |
Posts | : 23 |
Joined | : 2006-06-29 |
|
Profile |
|
Posted : 2006-07-01 15:47:35 |
Thanks, that's fine for now. |
|
|
|
|