I tested first with my GP 2018 (18.0.0496) and GP 2018 R2 (18.0.0672) environments, with the Inquiry->Purchasing->Transactions by Document process, filtering by dates of 1/1/1995 and 12/31/2028.
I see it first mention the pmBuildTrxInquiryByDateRange procedure you mentioned, then it does a series of calculations in a TEMP objects and the MC020103 table. It then looks at the zDP_PM30200SS_2 procedure and then a SELECT 25 from the PM00200 table. In my GP 2018 R2 environment, these two scripts ran 10x, but with the sample company, the process takes only 3 seconds.
My 18.00.0496 environment actually took longer, 6 seconds, but it still is running the same pmBuildTrxInquiryByDateRange procedure, then the calculations with TEMP and MC020103, before it begins the zDP_PM30200SS_2 procedure and SELECT 25 scripts about 10x, from the PM00200 table.
That being said, the sample companies most likely don't have anywhere as near as many records as the client has in the tables (Payables: PM30200, PM20000, PM10000, PM10300, and PM10400 - Receivables: RM30101, RM20101, RM10201, and RM10301), so hard to get a accurate test to re-create what you may be seeing.
Comparing the two versions of the pmBuildTrxInquiryByDateRange procedure, they're identical in the two versions, so nothing changed there. I'm curious what versions you saw when you looked at this procedure. I even looked at the GP 2018 RTM version, but didn't see the difference, or at least what the Beyond Compare application showed me.
Speaking with the GP 'Applications' team who look more at this process, they haven't seen the issues arise with the upgraded version, but there was a bigger fix for it in the year-end for GP 2018:
community.dynamics.com/.../microsoft-dynamics-gp-2018-u-s-year-end-update-released
If you want to look into it further, I'd recommend opening a support case so we can look at it. What we'd probably want to do is run a SQL Profiler trace on an 'old' and 'new' environment to compare the two to see why one takes almost no time to process and the other takes a minute or more.
If the client is already on 18.00.0672, other options they could look at using, as a work-around, are mentioned in the blog:
4. You could look at pulling transaction document by customer or vendor first as that will pull information quick and still allow you to use the Document number search, such as Inquiry | Purchasing | Transaction by Vendor or Inquiry by Customer.
The all in one viewers are nice too with drill back capability.
5. Review your data/tables and see how much history you may have and if you feel necessary look at archiving some of your older year data.
You may also consider created a historical company prior to remove this information, depending on your need/ use of the information.
Let us know if you have any questions. I'll be interested to see if others are running into this as well here on the forums.....
Thanks
Share
Report
75,730
All responses (
Answers (
