Well, today has been an interesting study in the API performance.
I ran multiple tests in three different configurations. This is my code executing, but the measurements are strictly from the API calls. Each test case was retrieved three times to get a sense of the general benchmark.
TestCases:
Case Name
OrderDtls
OrderRels
JobProds/JobHeads
Assemblies
JobOpers
JobMaterials
Case A
80
80
18
20
90
71
Case B
159
159
38
38
196
177
Case C
404
530
209
209
1377
1269
Test 1: Original SalesOrder and Job retrieval code using individual parallelized HttpCalls throttled to 16 simultaneous calls.
Case
Run 1 (ms)
Run 2 (ms)
Run 3 (ms)
Case A
3322
3211
3465
Case B
5249
5680
5069
Case C
9004
8066
8053
Test 2: Refactored SalesOrder retrieval utilizing slimmed-down $select params in $expand statements. JobEntry code is still original retrieval.
Case
Run 1 (ms)
Run 2 (ms)
Run 3 (ms)
Case A
1239
1137
1380
Case B
2566
2696
2455
Case C
6486
7038
6939
Test 3: Refactored SalesOrder and Job retrieval utilizing slimmed-down $select parameters in $expand statements.
Case
Run 1 (ms)
Run 2 (ms)
Run 3 (ms)
Case A
863
901
883
Case B
2203
1715
1988
Case C
5033
4973
5273
As you can see, reducing the trips to the APIs has a profound effect on the performance. I suspect these retrievals can still be optimized further, which I will also explore after adding the Purchase Order data retrievals. Note that because of the nature of the SalesOrder/Job relationship, the code to retrieve jobs for an order still needs to utilize the JobProdSearchSvc to obtain a complete list of job numbers. Those can then be passed to the Erp.BO.JobEntrySvc API for hydration. Using $expand with pared-down $select parameters, I was able to eliminate similar gymnastics in the SalesOrder child retrievals, which is why I suspect the greatest performance improvement overall came between Test 1 and Test 2.
Yeah would love to know getbyid on these same 3 large cases. He was at 1800ms on the OrderSvc/getbyid before so there’s possibility slimming fields via $expand($select) is not better.
I don’t appear to have the authorization on our system to create functions - either that or I don’t know where I’m supposed to go in Epicor to create a new one.
Unfortunately, I don’t have time to whip it up, but my thought was:
Create three BAQs
Have a BAQ for orders (Head, Line, and Release), POs (Head, Line, Release), and Jobs (Head, Assembly, Material, Operations) where each returning relevant information for a particular sales order. The BAQs will take into account all security but also be the method to restricting fields. POs are tricky in that you can have a Drop Ship from a Sales Order or Buy Direct on a Job, so you’ll have to handle that.
Write a function that either:
returns all three datasets or,
if you want to retrieve the three sets in parallel asynchronously, then one function that returns a single selected dataset.
The time consuming work would be in shaping the datasets most usable by the client. Are you planning to assemble the pieces in the client or would you prefer to have it returned as one blob?
FWIW, I’d say BAQs are kind of the sweet spot in the minimum customization approach for 3rd party integration. More than one big 3rd party integrator has simply passed a couple baqs for import and voila!
BAQs are compiled SQL queries, if I understand correctly, so perf should be good. You leverage server-side to control what you’re getting in the payload. More importantly, every company allows them, many users have the ability to import them, all without admin supervision (unlike functions).
That said, I’ve enjoyed highjacking this thread to learn about perf for all these options and hope you do try a function and finally compare all options to GetByID, if only for my selfish curiousity.
This has definitely been an educational interaction, yes.
The big win for me here has actually not been the performance, believe it or not. While that was the impetus for the thread and the performance increase is excellent, another significant benefit has been that two key classes are dramatically reduced in size and complexity. For those interested, here are the metrics for the two classes that make the calls:
Sales Order Service class: 179 LOC => 32 LOC
- Before Refactor: Cyclomatic Complexity 15, Maintainability 66, Class Coupling 25
- After Refactor: Cyclomatic Complexity 3, Maintainability 82, Class Coupling 10
Job Entry Service 171 => 111 LOC
- Before: Cyclomatic Complexity 18, Maintainability 58, Class Coupling 24
- After: Cyclomatic Complexity 13, Maintainability 64, Class Coupling 22
While the Job Entry service did not see as dramatic an improvement as the Sales Order service, all code metrics still benefitted from the refactor.
After driving to and from Cleveland this weekend, I wondered: does your client REALLY need the whole shabang or can you use some lazy/on-demand loading? How is this data going to be consumed? Many GraphQL people (rightly) argue that we bring down too much data in REST. But in REST, we could download just the data we need by defining new resources. One could download the Order and the Lines with hyperlinks to releases first. The lines would have hyperlinks to the releases, and the releases have the hyperlinks to the POs and Jobs. I was thinking this is a great case to be more RESTful and use HATEOAS.
