I have read the post to which you referred me each time you sent it. I have gone back and read/referenced it at least 10 more times. I have spent hours experimenting with every possible combination, but I am completely stuck on one area, so I hope you can help me.
Your references to dirty read fields, the unofficial list of which is in the referenced post, is acknowledged and understood.
I then set out to use fields not on the list with no success.
In the case of the bill table I can get around the problem by doing a fullsync on the billtopay table. That table is not too big and a fullsync doesn't take too long. But, in the case of the invoice table, a fullsync is not practical. There are 10,000+ invoices and it takes over an hour for that table alone. And, as you have said, generally speaking it is "silly" to do a fullsync everyday. The way I am building my application, it is an iterative process requiring Qbooks changes in between, so frequent fullsync of just the invoice table is not practical but I still need very fast queries.
So, I have narrowed my problem down to the invoice table, "subtotal" field. This does not appear to be or behave like a dirty read field. It does not turn "null" when that optimizer setting is set to null.
When I run "load updated data" on the invoice table combined with a NOSYNC query, the Invoice.Subtotal field does not see any of the updated data.
When I load updated data on the invoice table combined with a UNOPTIMIZED query, the Invoice.Subtotal field does see the updated data, but takes a lot longer.
So, I deduce from this that NOSYNC applies to all fields, not just the dirty fields, and tells QODBC to look at the last version of the data since a fullsync (hence the speed) and ignore the "load updated data".
Therefore, there is no way for me to use the speed of NOSYNC in combination with "load updated data" on any fields.
Since frequent fullsync on the invoice table is impractical, and the speed of SP_REPORT is also impractical, my only option are to use the slower "UNOPTIMIZED" option even with fields like Invoice.Subtotal. Correct?
For me, this means I will need to re-write my application to use the slower UNOPTIMIZED query one time into an array and reference the array thereafter from there (which is instant, but takes more programming). This is several hours of programming, so before I undertake that, please confirm whether I now have an accurate understanding of NOSYNC for non-dirty fields like Invoice.Subtotal.
The following was my unsuccessful attempt to get around the balanceremaining with non-dirty fields, but maintain nearly instant query speed because I repeat the query many times while the user is waiting (custom Excel spreadsheet function).
select sum(subtotal) from invoice nosync where ispaid = false
(above gives me the original amounts)
select sum(linkedtxnamount) from invoice nosync, invoicelinkedtxn nosync where Invoice.ispaid = false and Invoice.txnid = invoicelinkedtxn .txnid and linkedTxnTxnType <> 'SalesOrder'
(above gives me the credits/payments against the original amounts)
Net of these two numbers gives me the correct number, but NOSYNC does not work, only the slower NONOPTIMIZE.
I apologize in advance if you believe the answer to this question is obvious in the post you sent me. I couldn't see it as hard as I tried. Thank you.
|