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?
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.