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
|
|
QODBC Optimizer problem |
Author |
Message |
amir |
|
Group | : Members |
Posts | : 9 |
Joined | : 2006-08-17 |
|
Profile |
|
Posted : 2006-08-17 02:16:46 |
Hi,
We Have in QuickBooks (enterprise 6) over 5000 products, when executing with QODBC (V6) a select query the optimizer loads every item query all the products.
I know I can put it for loading at the end of last month and not every query, but in either selection the query takes more than 10 MIN!!!!
Why QODBC is SO SLOW?!
(We have the top of the line computer with 2 GIGA RAM and speed over 3 GHZ with a 10 GIGA free on HD)
Can you add an index to the QODBC optimizer? Can I add an index?
Please advise
Amir |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-08-17 10:39:08 |
|
|
|
amir |
|
Group | : Members |
Posts | : 9 |
Joined | : 2006-08-17 |
|
Profile |
|
Posted : 2006-08-17 18:46:56 |
Hi,
The items select query is:
select FullName,QuantityOnHand,QuantityOnOrder,QuantityOnSalesOrder,ListID,AverageCost from ItemInventory where {fn LOCATE('~', Name)}>0 and IsActive=1 and (CustomFieldon_hand_backup is null or CustomFieldon_hand_backup='' or {fn Convert(QuantityOnHand,SQL_VARCHAR)}<>{fn rtrim(CustomFieldon_hand_backup)} )
Also an example for very slow (1 min+) invoice query (we have over 50K invoices):
select * from Invoice where RefNumber='CHK20060817' and CustomerRefFullName='reger reger'
Can you (or me) add index (RefNumber,CustomerRefFullName) and an index for the top query?!
Please advise
Amir
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-08-18 13:14:33 |
Ok, for ItemInventory your query is doing a full table scan as the number of jump-ins (which act like indexes in the WHERE clause) is limited:
and combined with the fact that the query needs to evaluate the {fn LOCATE('~', Name)}>0 filter, this is always going to be slow.
The best way to run what you are doing is to do it like this:
Quickly Update the QODBC Optimized tables first by running:
sp_optimizeupdatesync ItemInventory
sp_optimizeupdatesync Invoice
Then run the queries (and any other ones you want to do) directly through the QODBC SQL Lite backend optimizer with the NOSYNC tag like this:
select FullName,QuantityOnHand,QuantityOnOrder,QuantityOnSalesOrder,ListID,AverageCost from ItemInventory NOSYNC where {fn LOCATE('~', Name)}>0 and IsActive=1 and (CustomFieldon_hand_backup is null or CustomFieldon_hand_backup='' or {fn Convert(QuantityOnHand,SQL_VARCHAR)}<>{fn rtrim(CustomFieldon_hand_backup)} )
select * from Invoice NOSYNC where RefNumber='CHK20060817' and CustomerRefFullName='reger reger' |
|
|
|
amir |
|
Group | : Members |
Posts | : 9 |
Joined | : 2006-08-17 |
|
Profile |
|
Posted : 2006-08-21 03:14:20 |
Hi,
I can do with out the expresion {fn LOCATE('~', Name)}>0 in my query.
When I remove above expresion the query is still slow (the result is only 5 rows).
Can you/me insert an index for (QuantityOnHand,CustomFieldon_hand_backup)
Amir |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-08-21 08:22:40 |
Sorry, Custom fields are not indexed and we don't yet support user defined indexes. |
|
|
|
amir |
|
Group | : Members |
Posts | : 9 |
Joined | : 2006-08-17 |
|
Profile |
|
Posted : 2006-08-24 03:02:59 |
Hi, What is the best way to know if the quantity on hand have been changed?! Is it to query the InventoryAdjustmentLine table? Or to camper it to a custom filed? Is there any other way? Please adivse Amir
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-08-24 09:52:24 |
Inventory Adjustments are seen in the InventoryAdjustmentLine table.
SELECT * FROM InventoryAdjustmentLine |
|
|
|
|