Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

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

Forum : QODBC Optimizer problemSearch Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC v6 Forum

 New Topic 
 
 Post Reply 
[1]  
 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

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2006-08-17 10:39:08

Sorry, you didn't paste in the select query, so I can't comment on the specifics. For further details on using the QODBC Optimizer see: How do I setup the QODBC Optimizer? Where are the Optimizer options? 

Please also update to QODBC v6.00.00.186 (when it's released).

 

  Top 
  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

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 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'

 

  Top 
  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
 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 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. 

  Top 
  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
 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 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

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to