Hi,

I have performance problems with a query:

select listid,name,QuantityOnHand from iteminventory where isactive=1 and PurchaseCost>0

The query takes a very long time about 30 sec until the first row comes, and extra time on every 100 records fetch.

The same query without the QuantityOnHand  field takes nothing including fetching all the records:

select listid,namefrom iteminventory where isactive=1 and PurchaseCost>0

Can I improve some how the first query?

Is there alternative (fast) way to fetch all the items QuantityOnHand ?

Amir

The use of  the QuantityOnHand column requires us to retreive the values directly out of QuickBooks in real time instead of using our optimizer. If you're happy you haven't changed QuantityOnHand, then you can bypass syncing with QuickBooks by doing this instead:

select listid,name,QuantityOnHand from iteminventory NOSYNC where isactive=1 and PurchaseCost>0

To resync the table run the following SQL statement:

sp_optimizefullsync iteminventory

See: How do I setup the QODBC Optimizer? Where are the Optimizer options? for more.

Hi,

First thanks for the fast reply.

I took your advice and now I execute the query:

sp_optimizeupdatesync iteminventory

and then execute the next query:

select listid,name,QuantityOnHand from iteminventory NOSYNC where isactive=1 and PurchaseCost>0

 To check that every things work I inserted manually 2 orders into QB with products ( active products with PurchaseCost>0) with quantity 4 and 3,

Then I ran the queries but the orders products QuantityOnHand is still showing the old QuantityOnHand.

 By the way the QODBC use optimizer is checked

with the radio button on “The start of every query”

 Please advise

Amir

If you're happy you haven't changed QuantityOnHand, then you can bypass syncing with QuickBooks by using the NOSYNC tag in the query. But here you are changing the QuantityOnHand so you will need to put up with the delay in running:

select listid,name,QuantityOnHand from iteminventory where isactive=1 and PurchaseCost>0

against QuickBooks in real time or resync the table again after the change by running:

sp_optimizefullsync iteminventory

or you can experiment using this instead:

select listid,name,QuantityOnHand from iteminventory VERIFY where isactive=1 and PurchaseCost>0

The problem here is that when QuickBooks modifies the QuantityOnHand it doesn't change the modified timestamp of the inventory record, so we can't detect the change..... that's why QODBC will forcible read the QuantityOnHand value directly from QuickBooks, which is slower.

Hi,

I understand what you saying. You are saying that you 'can't detect the change' of QuantityOnHand so my adivse for you on how you can improve your QODBC algorithm is:-

Update the items that have inventory transaction with date after the item inventory stored date (global stock last change date from last scan such as from the table InventoryAdjustment field TimeModified ).

And that’s how you can detect the change.

Amir