Anybody have a chance to help review the attached BAQ to see how we can get it to run faster? We tried using Each, First, and Last on the RcvHead and also tried using a Date criteria to help with performance but it doesn’t help. Any suggestions to help speed up the BAQ?
for each POMisc no-lock , each POHeader no-lock where (POMisc.Company = POHeader.Company and POMisc.PONum = POHeader.PONum ) , each PurAgent no-lock where (POHeader.Company = PurAgent.Company and POHeader.BuyerID = PurAgent.BuyerID ) , first RcvHead no-lock , each Vendor no-lock where (RcvHead.Company = Vendor.Company and RcvHead.VendorNum = Vendor.VendorNum ) where (POHeader.Company = RcvHead.Company and POHeader.PONum = RcvHead.PONum ).
aidacra
(Nathan your friendly neighborhood Support Engineer)
2
What is your objective with this BAQ? As in, what data are you trying to display? My gut tells me that POHeader should be the first table as one could have multiple POMisc records per POHeader.
Are you trying to put this into a report / dashboard / quick search?
How long is it taking to return?
How many records are being returned now?
SQL / Progress? If SQL, have you looked at the execution plan?
Without context, this should likely be faster but, I suspect the logic itself can be optimized:
FOR EACH POMisc WHERE company <> '' AND PONum <> 0 AND POLIne <> 0 AND SeqNum <> 0 NO-LOCK ,
FIRST POHeader NO-LOCK WHERE (POMisc.Company = POHeader.Company AND POMisc.PONum = POHeader.PONum ) ,
FIRST PurAgent NO-LOCK WHERE (POHeader.Company = PurAgent.Company AND POHeader.BuyerID = PurAgent.BuyerID ) ,
FIRST RcvHead NO-LOCK WHERE (POHeader.Company = RcvHead.Company AND POHeader.PONum = RcvHead.PONum ).
FIRST Vendor NO-LOCK WHERE (RcvHead.Company = Vendor.Company AND RcvHead.VendorNum = Vendor.VendorNum )
We are trying to pull back all Misc charges associated with Purchase orders at the header level.
Put this into a dashboard
10+ minutes in test environment but in Production, it doesn’t return and gets hung depending on date range selected
It times out and error message pops pu so it doesn’t return any in the BAQ
We are on SQL. How do we run the execution plan in SQL Studio? I can send the info to our SQL administrator to review but what do I need to provide them to look at the execution plan to review?
One thing that might speed up your query is to change your criteria from " WHERE company <> ‘’ " to “WHERE company = CurrentCompany”.
Note: You can find the “CurrentCompany” field value by selecting the “BAQ special Constant” Filter Value option and looking for it lower on the given list.
We’re going to try to reindex the tables to see if that helps with performance. I believe it hasn’t happened in the past and we haven’t reindexed for about 6 months now.
