When making a BAQ that will use a Inner-SubQuery (ISQ), does criteria in the top level affect the processing of ISQ?
For example, say I have a BAQ with:
An ISQ (named JobEstCost) that rolls-up the estimated cost of a job. (Or does something very computationally intensive)
The ISQ’s display columns are:
JobHead.JobNum
OrderNum (a calculated field from the JobNum)
OrderLine (a calculated field from the JobNum)
OrderRel (a calculated field from the JobNum)
EstCost (the rolled-up cost of the MOM)
The Top level sub-query
with table Relations:
With no table or sub-query criteria set anywhere, it runs full out.
If I set a table criteria like OrderRel.OrderNum = 2200240 It only returns the rows for Order 2200240. But did it have to process the ISQ query for all Jobs?
If I add another table criteria, this time in the ISQ, of JobHead.JobNum LIKE convert(varchar, 2200240)+'%'
The ISQ only runs for a subset of jobs (ones beginning with '2200240') I assume this is much faster than when the ISQ computes the cost for Every Job, but then only provides the ones with the table relationship specified in the Top Level
My main concern is that if this is to be used in a BAQ or embedded dashboard, It won’t have those table criteria. Would the BAQ run full out, and then only return the records based on the Dasboard’s filter/criteria?
If you have two BAQs in one Dashboard… BAQ 1 publishes a value, and BAQ 2 FILTERS based on the published value, it will refresh BAQ2 every time you change your focus in BAQ1. The filter is applied BEFORE running the query, so it will (or should be) fast, UNLESS the field you are filtering on in BAQ2 is not indexed.
BUT… you can also do a trick here. when applying your filter to BAQ2, there are two ways to apply it…
Apply the filter at the QUERY level in the dashboard - causes the query to re-run
Apply the filter at the GRID level in the dashboard - query NOT rerun, but ALL the data was retrieved and then filtered.
There are some advantages to the GRID filtering… the dashboard can work much faster once the data is retrieved. The data only needs to be retrieved one time.
I’m not a BAQ expert, but if you can trust the pseudo SQL that is generated in the BAQ query phrase … the subquery is running through all the unfiltered records in the subquery and then joining with the top level query. I believe this is part of the reason that Epicor BAQs are not very scalable. To get around this and use the power of SQL, we generate table valued functions and utilize external BAQs. In SQL, you can use windowed functions like CROSS APPLY which can reduce response time on queries significantly. However, the problem with external queries is that they can bypass the security model in Epicor so I’m not sure this is the best way to do this.
I would cite the standard answer to questions about SQL statement performance - ‘It depends’
The query optimizer is smart enough to look at a parent table filter. Depending on query complexity and a relation configuration it can apply the filter to the child table too.
I’ve checked your case on my synthetic query sample, where I select from Ice.QueryHdr table and join with a subquery, where I calculate the number of rows in Ice.QueryField table by using COUNT aggregate function and grouping by Company and QueryID columns. Table and subquery are inner joined by Company, QueryID pairs.
If I apply CompanyID = ‘my_query’ filter to QueryHdr table, then the actual execution plan shows me that this filter is applied to selection from the QueryField table. So, extra calculations are eliminated.
As usual, you can get the answer to your question from the query execution plan and amend the query design appropriately for the better performance (or tweak table indexes, or drop statistics, or whatever else).
Query phrase shows not the actual query SQL statement but a simplified version. Some technical and security details are omitted. But of course, you can catch the real SQL statement by using dedication SQL tools.
If the query optimizer was not managed to propagate the filter down to child subqueries then you can repeat the filter in these subqueries. Using a query parameter may help avoid repeating a constant value across the query.
Here is a trick I have done multiple times…
Trick, you need a the same “constant” or parameter to be used on multiple sub-queries… but you dont want to create that constant on every sub-query, because you may need to change it. how do you set this constant?
create a new sub-query called “MyCostants”.
in your new sub-query, do NOT have any tables… simply create Calculated fields for each constant you want to define. Example:
StartingDate
EndingDate
TodayMinus60Days
TodayPlus90Days
On every other sub-query, include this “MyConstants” query, and use the values from it for filtering your sub-query data. This will then pre-filter those sub-queries to your constants.
you can also use those constants for other calculations.
Example, i created a set of constants for 30-60-90 ARAging report. I needed to calculate the date 30, 60, and 90 days back to do aging calculations and put data into the correct column. I had sub-queries that all needed these same dates.
