I have a query that was working just fine, we had a request to add some fields to the query and I now can’t get it to execute (locks up my machine). The original query already had a subquery where total quantity on hand was being calculated for each part, so just totaling all of the different onHandQtys in each bin for each part.
I now have added a new subquery to calculate the total received quantity of each part for that past year and this aggregate function has rendered the query inoperable. Are there any other tricks that anyone has figured out that might help a query to run more efficiently when it comes to aggregate functions? Or is there another way anyone knows of that I could total quantities for each part without it being a subquery? I feel like I’ve made much more complex queries with no issues, so not sure why this calculation bogs the system down.
Have you tried setting that subquery as a cte instead of inner to see if that helps at all? If you run the subquery as the top level does it come back or time out?
So far it’s looking like it’s running at about the same pace after changing the query to a CTE query. I did finally get my results, 800 rows took a little over 5 minutes to load. The subquery itself runs with 3,000 rows in a quarter of a second.
I need another field from RcvDtl. I originally had the subquery joined to part and then RcvDtl joined to the subquery but switched those around to try and join on company. The subquery does not have Company in it.
If you need rcvdtl separately then directly join from part to the subquery. Chaining those together you are running the sum individually for each and every rcvdtl record that is returned. And always make sure you include company in the subquery and ALWAYS join on company. It doesn’t matter if you only have one company.
I’ll politely semi-disagree with that. Always try joining on company. However, sometimes (even usually) that also tanks query performance for inscrutable reasons. So also try joining without. Go with whichever is faster.
I think I’m still pretty much in the same place query wise.
@Banderson, I tried the recompile and after 5 or so minutes I’d get an error and 0 results. I removed it from the execution settings and am back to getting 800 results in 4 minutes. I’m on 2023.1.10 by the way.
It said something like Application Error: Application Server cannot be reached. Something to that effect. I ended up just splitting the query back up into my original query and made a second query containing the fields that were newly requested. Then made it so that the grid containing the new fields was “subscribed” to the original grid. It seems to work, still waiting for hear any feedback the users have.
That’s what “Specified table field value” is for. Just make sure the order of your tables puts the needed table first. Like this - this is a criteria on PartCost that is referencing Part:
