Improve performance of WareHouse query on Exact Online

Question

We have 20 warehouses and 3.000 articles. Therefore there are 60.000 rows in the ItemWarehouses table of Exact Online. However, retrieval takes 1200 ms per 60 rows, so total query on this data volume for a warehouse analysis takes 3-4 hours.

I've tried to restrict the number of data retrieved using the following filter because we are only in items with some non-zero stock information:

select t.* 
from   exactonlinerest..itemwarehouses t
where  ( currentstock != 0 or projectedstock != 0 or plannedstockin != 0 or plannedstockout != 0 or safetystock != 0 or reorderpoint != 0)

But it still downloads all 60.000 combinations and filters them on the PC. The result at the end is approximately 700 valid combinations of warehouse and item stock information.

Is there a way to retrieve the data in a more performant way?

Answer 1

Invantive SQL does not forward OR-constructs to the server-side. But in this case you might want to change the OR into a UNION (without ALL):

select t.* 
from   exactonlinerest..itemwarehouses t
where  currentstock != 0
union 
select t.* 
from   exactonlinerest..itemwarehouses t
where  projectedstock != 0
union 
select t.* 
from   exactonlinerest..itemwarehouses t
where  plannedstockin != 0
union 
select t.* 
from   exactonlinerest..itemwarehouses t
where  plannedstockin != 0
union 
select t.* 
from   exactonlinerest..itemwarehouses t
where  safetystock != 0
union 
select t.* 
from   exactonlinerest..itemwarehouses t
where  reorderpoint != 0

These filters are forwarded to Exact Online and should run very fast given your data distribution. The UNION ensures that you only get the unique rows back.