In BAQs are SubQuery Criteria preferred over Table Criteria? Or other way around?
What context? Some places you need one or the other. If it doesn’t matter, try both and see what the execution time is.
@Wwendel I was just playing with this and found that table criteria performed the best. I believe that this is because LINQ uses the order FROM … WHERE … SELECT.
I have found that putting in BAQ criteria is even faster than joins as well.
@Dmitry_Kashulin Is this correct or do you have a recommendation for criteria vs join performance?
What are the different contexts that might require one approach over the other?
if you use some calculated fields that puke on null, for example, you may need to filter the table first. Other specific cases are escaping me right now, but I know that many times I’ve had to use one or the other for various reasons. Usually I default to table unless I need query.
Thanks for your response. So if performance is an issue, i’ll look into using table criteria.
so the lesson here is certain activities may only be supported in one level or the other; so you must use trial and error. i think i ran into this once already, but now i know why. Thanks.
It’s not really trial and error, it makes sense if you are filtering before or after the joins, you just have to work through the logic. A lot of cases, you could do either. But there are some where you have to pick one for specific reasons.
A typical example of when a subquery criteria is required is when you want to filter on a calculated field which uses fields from more than one table.
1 Like
SubQuery criteria are useful if you want to filter by a calc field output. There is simply no other way to do such filtering.
There is no right answer to the question about Join vs. Criteria. BAQ Designer does not allow you to put tables into a subquery without relation between them, this limitation is protection against huge query output due to cartesian product (cross join).
Criteria applied to a “second” table in the relation always goes into the join condition in resulting sql. Sometimes a table criteria gives SQL Server query analyzer a hint and more effective execution plan is generated in result.
2 Likes