I have a RDD BAQ Report that has three separate but almost identical queries. In my SSRS report I actually Union these queries together to show inside of one tablix.
What I Know.
This report would be better suited with a Data Warehouse because of the amount of data that I’m querying at once.
(I just need to get A solution working for now)
This report IS WORKING when I run it for a single day.
When I test the BAQs individually with the Execution Settings timeout set to 9999999, I get 2 of the 3 BAQs to run in under 30 seconds. The other takes 8 minutes.
Ignoring the fact that one of these queries only takes 8 minutes, shouldn’t the report still run? even if it took 15 or 20 minutes, why does it timeout?
Unless this is a cross company BAQ, try to put a table filter on the first table of your long running query (or all 3 first tables in your union stack) that is Company = CurrentCompany in the BAQ special constants.
When you get the queries to this point, sometimes it will help. If you already have it set this way then we need to focus on the 8 minute query. Is it just 100 pounds of data or complex joins and/or subqueries…or both? Sometimes pushing some data down to a subquery helps.
The structure of my query is this. A single top level BAQ that is grouped to show all the days of a given time period that we have shipments. All the other fields are inline subqueries to show different metrics about that day. On Time, On Time & In Full, Etc.
This is not a cross company BAQ. And I have read up on some of the current issues with the BAQ engine inside of Epicor 10 that should hopefully be fixed in 10.2.7 and higher.
The 3 BAQs are almost identical, expect each one has a different way to define a “business sector” and 1 different calculated fields between the 3.
Yes the queries are limited with a date range parameter.
While I understand a 8 minute runtime on a BAQ is horrible, and I do want to improve the performance. The query works as I want it to in its current state… My main question/beef with it is that the report doesn’t run when I run it for more than 1 days worth of time. Epicor kills the process before it can finish. What settings do i need to change to allow the report more time to run?
I have read up on some of the threads in regards to the issues with the BAQ engine in versions pre 10.2.7. I will try your suggestion, but still am confused why epicor wont let it finish out the report render in system monitor.
I don’t have any indication currently of why its taking forever. Its identical to another query in the union stack expect for a PrimWhse criteria. And the other takes 30 seconds. They both have a similar amount of shipments that they report on which confuses me more.
Okay, thanks. Does it give you an error in the system monitor or the event viewer on the server?
We have a couple of large queries that run and experiments with the timeout setting in the BAQ designer let it run for 15 minutes, if necessary. All we have to do is change that value.
