To cut to the chase, I am dabbling with an Epicor Function, and it works but takes 3.5 minutes just to GetRows.
In this example, I only need to return 42 “rows” of data. And I get 42 releases, but I also get 1,250 OrderHed records.
So, is there a way to only get 42 OrderHed records? Because I feel like this is the source of the slowness, and it’s certainly extra data I don’t need.
Now for the details.
I made me a function. (I added the MiscChargeCount after the previous screenshot.)
It gets some rows based on some where clauses (which for laziness I made as inputs to the Function):
And then it counts how many rows were returned for some of the tables in the SalesOrder tableset:
If this were a BAQ, it would look like this:
And take milliseconds to get the same 42 rows:
Can I get the same speed in the Function? Am I misunderstanding the where clauses?
EDIT and summary - August 3 at 5:04 PM (EST)
To put a bow on this, where clauses are definitely not the most efficient things, and many others suggest better ways in the replies below.
But to answer the root question, WYSIWYG.
- A where clause on the header affects the header and everything below it (like lines and releases).
- But a where clause on the line (OrderDtl) will not reduce the number of OrderHed “rows” returned; it only reduces the result-set of lines and things below lines (like releases or [some] misc charges).
The where clauses on OrderHed, OrderDtl, and OrderRel do work together, but only downward; not upward. As you see, I get fewer and fewer results (which is the goal) the more I go down the hierarchy.
It results in a very bizarre dataset of lots of parents with no children or grandchildren, when the goal is just to get the grandchildren. And on that creepy note, good night.