Putting the “where” clause in the SubQuery Criteria section didn’t’ seem to impact performance (the query still fails to run), but for future reference, is this where table criteria should be placed? I have not had to do that before, so if I’m using the tooling wrong I’d really like to know!
@josecgomez I updated the table statistics but no performance impact. Now, the query won’t run even without the table criteria applied 
it’s gonna be one of those days, isn’t it?
what is the execution plan now?
No normally Table Specific filters should be added to the table, otherwise when you put the whereClause in the Subquery tab it runs the whole thing and then filters it which is inefficient. (IMO)
Ok try this old hack, remove Company from the joins (I know its counter intuitive, but there was a weird bug with this in a few versions)
Or…
Also try flipping the order of the tables from Most Restrictive to Least Restrictive to help the Db Engine
Look like you want only open quotes, move the QuoteHead table to position 1 in the Query, then QuoteDtl 2
Part 3 and UD100A 4
You can do that from the Table list tab.
PS: You can combine any of these with the OPTION (RECOMPILE) hack to make sure you get a fresh query plan… LOL #SoManyOptions
How many records are we talking here by the way?
How Many open Quotes? / parts etc
This query is totally cursed, I am going to scrap it and try again.
Total records returned should be around ~500 so not a ton. The UD100A table has about 95K rows, so maybe that’s part of the hold up. Part has 224 recs. QuoteDtl 79K
Thanks for everyone’s help on this.
I ended up rebuilding it slowly and making sure it worked along the way. Of note, I wrapped UD100A in a subquery instead of as a direct table join and used the SubQuery Critera to match it’s values to the specific field values of QuoteDtl
The UD100A subquery itself is wide open inside the subquery, spitting out 3 fields, which I then either joined on or filtered on in the top level query.
Not really sure what do think about this one other than that something was super funked up along the way, but I really appreciate everyone who helped me out here!
Did you try to join the Part to the quotDtl instead
Just for the record, we have had 600.13 BAQ X-Company Performance issues as well.
https://epicor-manufacturing.ideas.aha.io/ideas/ERP-I-171
Added OPTION(RECOMPILE) and seemed to do the trick. Thanks @josecgomez that screenshot helped.
CC: @Rich
