We recently had a support case regarding very poor performance in the Dynamics GP Payables Transaction Inquiry – Document window. The client has a very high volume of transactions so the payables transaction tables contain millions of records.
The user reported that the window worked fine in GP2015, but in their GP2018R2 test upgrade environment, using the window froze the GP client and severely degraded performance in the system for other users. The user had to forcibly kill the GP client.
Our investigation revealed the following.
The window uses a SQL stored procedure to populate a temp table which is used to fill the scrolling window. The stored procedure (pmBuildTrxInquiryByDateRange) was not using the “no lock” hint, so the query’s table joins looked like this:
There are several queries in the stored procedure that use different tables, none of which use “no lock”.
The client reported that the performance issue was resolved after we updated the stored procedure to use “no lock” in all of the queries, like this:
