We have customized the SalesOrder endpoint. We’ve noticed that for some orders, it takes a “long time” for the json to be returned. In this one case, an order is taking 10 seconds to be returned.
I’ve determined that it has something to do with combinations of the detail elements that we want expanded.
| Elements included in expand | Seconds for data to be returned |
|---|---|
| Details | 1.1s |
| Details, CustomDac1 | 1.27 s |
| Details, CustomDac1, ShipToAddress, Payments | 4.18 s |
| Details, CustomDac1, ShipToAddress, Payments, CustomDac2 | 11.32 s |
| Details, CustomDac2 | 2 s |
| Details,CustomDac2, ShipToAddress | 1.67 s |
| Details,CustomDac2,ShipToAddress,Payments | 1.97 s |
| Details,CustomDac2,ShipToAddress,Payments,CustomDac1 | 10.49 s |
| Details,CustomDac2,CustomDac1 | 9.11 s |
So, CustomDac1 with Details = 1.27 s. CustomDac2 with Details = 2 s. Details, CustomDac1 and CustomDac2 is 9.11 s.
When I expand CustomDac1 and CustomDac2 together, something happens and the query times increase substantially. But when they are each run individually, it’s a reasonable speed.
It would be faster to query the API twice.
I’m assuming that I’ll start with a SQL Monitor exercise but given my quick testing results above, the issue isn’t with database performance. Is there anything else I should look towards?
CustomDac1 is simple view on a table I created. The fields being used in the where clause are indexed in SQL and is not the primary key on the table.
CustomDac2 is a view that incorporates a projection. This is from a third parts and I have little control over it. But I did add an index to the table that both the view and the projection are pulling from. Again, the fields being used in the where clause are indexed in SQL and are not the primary key on the table.
