...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
Requirements: 
MODORDERS-488 Investigate transition of Orders search to cross-index subqueries
Overview
...
API with views:
/orders-storage/
...
receiving-
...
history →
...
receiving_
...
history_view
Receiving view totally differs from other views. Data for this enpoint totaly builds on the fly and don't has any own tables for persisting data. It could be switched to the cross index approach, but in this case instead of single database request there will be 3 requests which will return a large amount of data (collections of pieces, order lines and purchase orders). Making multiple requests and processing such large amount of data (which might be containing hundreds of elements withing each collection) wil not give any perfomance improvement and will consume a much more amount of memory.
For now we have to leave it as is.
/orders-storage/orders → orders_view
View and API implementation can be removed at all. All requests for retrieving collections of orders should be routed to the endpoint "/order-storage/purchase-orders" where cross index approach already implemented.
Will require backend story in buisness module for this purpose.
Will require UI story for support cross index queries upon calling
/orders-storage/po-lines → po_lines_view
Will require backend story in buisness module for this purpose.
Will require UI story for support cross index queries
Prepared a POC with migration of this endpoint from searching accross the views to the searching via cross-indexes. This POC contains migration to the cross index approach and will signifcantly reduce amount of work in storage modules.
/orders
...
-storage/order-lines → order_lines_view
View and API implementation can be removed as well. All requests for retrieving collections of PO lines should be routed to the endpoint "/order-storage/po-lines"
Performance improvements:
For the perfomance testing purposes I have prepared data loading script which creates 100 purchase orders and 50.000 PO lines in total. Each poline slightly differs from other only by listUnitPrice, so search requests will be performed quering this field by value.
Results of the search across the views:
Query example /orders-storage/order-lines?query=workflowStatus==Pending and cost.listUnitPrice==21.0
As you can see every search requests takes almost 2 seconds
Results of the search via cross index approach:
Query example differs a bit according to cross index syntax: /orders-storage/order-lines?query=purchaseOrder.workflowStatus==Pending and cost.listUnitPrice==21.0
As you can see every search requests takes less than 200 ms
So switching implementation from views to the cross index search improves perfomance about 10 times.