I’m having an issue with this new feature. If the Referenced Query returns more than 10,000 rows the BAQ fails.
I’ve always wanted to create a standard BAQ for returning a Part’s current revision and use it in multiple BAQs. In my first attempt, I ran into the 10,000-row issue.
We have over 15,000 manufactured parts with revisions.
I tried setting the “RemoveTestRowLimit” to true in both BAQs but that did not solve the issue.
EpicCare reported the issue as a bug to development, but development rejected it.
The suggested solution was changing the application server’s max rows setting. I do not feel that is a workable solution or a good practice.
In all cases there is a balance with this. reference queries are effectively being built as temp tables that means all the data has to be built and contains somewhere eating server resources before the outer BAQ can run. There is no ‘happy size’ for this. Whatever we decide on will be too small for some, To resource intensive for others. We appreciate feedback to help us try to find the point of least annoyance for most of you that doesn’t overload everyone’s SQL servers.
in the meantime it BAQ parameters can be created on the referenced query and exposed in the wrapper query - is there by chance a set of parameter(s) you might be able to use that can be used to reduce the result size of the sub query to a more reasonable number avoiding the limit or the size you set on it? That is not always going to be the case but it is a good place to start- setting up your sub query so you can have it return potentially all of those rows, or grouped subsets that make sense for the outer query.
yes, we are still working on a solution to this “noisy neighbor” problem. What we WANT to do is to “throttle” any BAQs that might be consuming lots of time, rather than LIMITING the results. Currently we do not have a way to monitor and throttle (slow down) any user who is consuming lots of CPU Resources. The challenge is that anyone that is consuming lots of resources will low down their co-workers and other “neighbors” causing people to think that the system is no performing. We added the limit for now to help until we can resolve to a better solution.
That’s the right approach. It’s roughly as easy to consume all available RAM to return a single bit, as it is to bogart all the IO with GiB of results, or bind up processing resources.
Fortunately, Resource Governor is an existing component of SQL Server that is quick and easy to apply.
An interesting observation on this. I tried referencing a BAQ that did NOT have a CTE but has a Pivot. The Top level BAQ failed wit the error " The scale (7) for column ‘SubQuery1_1’ must be within the range 0 to 6" .
Bug or am I missing something ?
EDIT: Seems Referencing SubQuery as a CTE will not Error out…
Probably you have discrepancy between decimal values your query returns and format for them. So when temp table is created for the referenced query, its data type for SubQuery1_1 is not specified correctly. Try to change format for SubQuery1_1.
In my own laziness, I set the field as decimal but formatted it as <999,999,999. This has to do with forecast and we don’t forecast part of a part. I am the one looking at the BAQ and didn’t want to see the decimal points.
Back to Basics… Data source gets the data, UI formats to look ‘pretty’ BAQ designer not a UI…
Lesson learned albeit the hard way…
Thank you Olga for pointing me in the right direction !!
