I’m working on a BAQ for cycle counting that should list every active part and some of the fields should include the last qty-adjustment date as well as the date of the last stock related transaction.
I’ve tried using a subquery with only partTran to find the max tranNum for the given transaction, then joining with another copy of the partTran table as well as calculating row numbers and using criteria to only select the most recent row but both approaches appear to look at every row in the part transaction table for the given part number as they are extremely slow(BAQ times out). I’ve double checked all my joins and even had an Epicor consultant take a look at my query for help(did I have a join problem or maybe there was a more efficient approach) and we couldn’t figure out how to speed it up. Any advice on what approach is most efficient to solve this problem?
Might not be the solution for you, but on versions prior to 11 there is an optimization issue with the BAQ engine. Its not recommended as you shouldn’t have to specific company criteria, but it gives a noticeable boost in some cases. Take a look at my findings
Any calculated fields? SQL sucks at functions when it comes to speed