I would suggest to go with apis approach instead of querying database directly. It is safer and more future proof. The relationships between shipments and lineitems are handled at api level anyway

@Quan

Thanks, while going down the API root is possible these are going to be heavy queries and surely benifit from query execution plans and optimisation. I'm not sure how well episerver could handle thousands and thousands of orders?

For the purpose of report which is usually run only daily at low traffic, loading orders by batches of thousands should not be a problem

Hi Quan,

I cannot guarantee when the reports will be ran! And as the requirements are for the report to be outputted into CSV there will be no pagination, so in the case all orders (between any possible date range) will be loaded. This is my concern.

I don't get it. What is the problem with loading by batches and process data and save to the csv files when you are done with a batch?

• Hit the database to get total rows using FindPurchaseOrders, this in turn calls [ecf_Search_PurchaseOrder] which in turn calls [mdpsp_avto_OrderGroup_PurchaseOrder_Search]
• Hit the database in calling [ecf_Search_PurchaseOrder] which in turn calls [mdpsp_avto_OrderGroup_PurchaseOrder_Search] with search parameters
• Repeat the above step multiple times per batch
• Iterate/transform results

As you can see its going to be pretty heavy on the database!

Reporting is supposed to be data heavy. Unless you run your report every few minutes it would not be a problem. I don't know what you are doing with your reports but sales data can "appended" - for example daily, instead of getting full data every time