We have a baq that runs daily to give us the requirements on orders for the day. It does a bom explosion to get info off of material part. This is very slow (4mins). How can I speed this up?
If you post a detailed description of how the BAQ works, or the BAQ itself, someone may be able to see somewhere you can improve it.
We utilize the kanban receipt system for all builds, so no parts are allocated to a job. In doing so, we have a custom cell report that tells each assembly cell what order are open for the parts in their respective cells. To capture expected prodstds for the subs (phantoms) the report does an indented (CTE) BOM. and pulls in prod stds accordingly. The top level sums these prodstds up for the respective cells and gives a mins to build based on the order qty.
SOTEST6.baq (157.7 KB)
I’ll pass off the same advice I’ve been given. Get rid of those DISTINCT clauses. If you can get your filters correct, you shouldn’t need them.
3 Likes
I’ve tried this with no success unfortunately. Any tips on the easiest method to filter them down?
Try a stepwise approach. Take apart your BAQ, and run each subquery as the top level to ensure each subquery returns the expected values. If a subquery returns duplicates, resolve them until each subquery returns only unique records. The only way I found to resolve them is to try to filter down each subquery to return the fewest results possible (and still get the outcome you need).
Go into your BAQ > Actions >Execution Settings. Add a new Setting Name = ShowStatistics, Setting Value = True. This shows some useful information about how many records are pulled from the various tables in your BAQ. I found that some of my huge slow BAQs have record counts in the millions, when I was only returning a few hundred rows. After I rearranged my BAQ to return the minimum rows in each level, the whole thing became lightning fast. It is so satisfying to get it right. Keep trying!
Tracking Down a Speed Issue - Kinetic 202X - Epicor User Help Forum (epiusers.help)
Lots of good tips in this thread.
Good luck!
3 Likes
Statistics and actual execution plan were not available on that old version yet -
10.2.700
1 Like