I have a cross-company BAQ that was working fine for months, until a couple of days ago. Now it’s suddenly timing out. (Ok, I don’t know exactly how ‘well’ it was working previously, just that it wasn’t hitting the 30-second timeout limit and now it is.)
If I switch it to single-company it executes in ~300ms. Here’s the strange thing: If I add a Table Criteria to show data from 2 companies, it still takes <1 second, but if I up it to 3 or more companies, it times out. Every Join has the relation Company=Company set. And if I run just the SubQuery as a TopLevel, it returns in 400ms for all 5 companies. So the SubQuery seems to be ok on its own. Is there something that could be improved with the link from the SubQuery to the TopLevel? Or am I missing something else here?
Here’s the top level query, highlighting the link between the SubQuery and the Vendor table:
try turning the sub query “TopVendors” into a CTE Query instead of an inner sub query so that it only needs to run one time. it might go faster.
also, wondering why your sub query is also retrieving the vendor table again. Why not have the subquery link to the supplier number directly in the rcvhead instead?
@timshuwy: Thanks for the tips! Eliminating the Vendor table from the SubQuery got the execution time down to ~13s, which still seems slow but is good enough. I also tried turning it into a CTE, but that didn’t give any further improvements.
@Patrick: Unfortunately, or last Pilot snapshot is over a year old, and doesn’t have a copy of this query to test.
