logo
Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
Matt Pfountz  
#1 Posted : Tuesday, July 3, 2018 1:52:28 PM(UTC)
Matt Pfountz

Rank: Administration

Groups: Administrators
Joined: 12/19/2017(UTC)
Posts: 1

Was thanked: 1 time(s) in 1 post(s)
Inquiry>>Purchasing>>Transaction By Document

The Payables Transaction Inquiry by document window added additional sorting options in GP 2018. As a result, if you have a large number of historical transactions (greater than 100K) you may notice the window is very slow to open.

I ran a SQL profile and determined that it is filling the scrolling window on open with the results of the pmBuildTrxInquiryByDateRange stored procedure. The silly part is that the proedure is called when the window is opened with no filters so all documents are returned. For my customer, this meant that the window took 1:25 to open.

My (hacky) fix was to modify the GP procedure and add a check to see if there are any filters being passed in. If there are not, I just return.

Please note, this is completely unsupported and will have to be reapplied after every service pack or upgrade. But if you have a lot of documents, this is a decent workaround. I have submitted this to Microsoft and they added it to the backlog to be fixed at some point. Because the stored procedures are all on one line, you'll need to add some carriage returns to put find the correct location. It goes right after the stored procedure parameters.



if isnull(@I_StartDocNum,'') = '' and isnull(@I_cStartVendorID,'') = '' and @I_dStartDate = '1900-01-01 00:00:00.000' begin
return
end


Enjoy
Matt
Matt Pfountz
thanks 1 user thanked Matt Pfountz for this useful post.
dfinley on 9/7/2018(UTC)
dfinley  
#2 Posted : Friday, September 7, 2018 2:43:18 PM(UTC)
dfinley

Rank: Newbie

Groups: Registered
Joined: 9/7/2018(UTC)
Posts: 0
United States

Thanks: 1 times
Hi Matt,

Do you have a log number from Microsoft for the issue you've submitted to them for this? We're seeing the same issue in our GP 2018 testing, although in our largest company with millions of transactions, the Inquiry Window doesn't ever load (well, I gave up trying after 30 minutes). I've also seen the templog.ldf on the SQL server grow enormously while this is happening.
Matt Pfountz  
#3 Posted : Friday, September 7, 2018 3:00:46 PM(UTC)
Matt Pfountz

Rank: Administration

Groups: Administrators
Joined: 12/19/2017(UTC)
Posts: 1

Was thanked: 1 time(s) in 1 post(s)
I do. It is incident # 118070318511128 and has been reported as a bug to be fixed. My workaround should work great for you though. If you'd like, I can show you how to implement it in your test company. Just let me know. My direct email is matt.pfountz@interdynartis.com .

Matt
Matt Pfountz
dfinley  
#4 Posted : Friday, September 7, 2018 3:11:54 PM(UTC)
dfinley

Rank: Newbie

Groups: Registered
Joined: 9/7/2018(UTC)
Posts: 0
United States

Thanks: 1 times
Thank you Matt,

I've added your change to my local test environment and confirmed it works great.

This looks like a pretty massive change from GP 2016, as far as I can tell it looks like the code back then was just starting with the top 25 records from the PM00400 table.
Matt Pfountz  
#5 Posted : Friday, September 7, 2018 3:16:19 PM(UTC)
Matt Pfountz

Rank: Administration

Groups: Administrators
Joined: 12/19/2017(UTC)
Posts: 1

Was thanked: 1 time(s) in 1 post(s)
Awesome!

Yeah, they definitely changed the behavior of the window. They added some new filters and didn't account for performance. See below. If you contact Microsoft and complain and reference my ticket it may help it get fixed faster.

https://community.dynamics.com/gp/b/dynamicsgp/archive/2017/12/12/microsoft-dynamics-gp-2018-new-sort-options-on-many-windows-giving-the-user-easier-access-to-find-information


Matt
Matt Pfountz
irishjamr  
#6 Posted : Wednesday, February 6, 2019 3:58:52 PM(UTC)
irishjamr

Rank: Newbie

Groups: Registered
Joined: 2/6/2019(UTC)
Posts: 0
United States

Thanks for this post; works great for us. Any chance you have a similar workaround for the Receivables Document Inquiry window?
Matt Pfountz  
#7 Posted : Wednesday, February 6, 2019 5:21:09 PM(UTC)
Matt Pfountz

Rank: Administration

Groups: Administrators
Joined: 12/19/2017(UTC)
Posts: 1

Was thanked: 1 time(s) in 1 post(s)
Awesome, glad to help. I haven’t noticed the issue on the receivables inquiry. If you’d like me to look at it with you send me an email and we can investigate. Matt.pfountz@interdynartis.com
Matt Pfountz
dfinley  
#8 Posted : Wednesday, February 6, 2019 5:40:22 PM(UTC)
dfinley

Rank: Newbie

Groups: Registered
Joined: 9/7/2018(UTC)
Posts: 0
United States

Thanks: 1 times
The issue of slow performance of the Inquiry | Transaction By Document for Payables or Receivables windows was supposed to have been resolved in the 2018 Year End Update. I haven't confirmed yet, but it's on my radar to test soon - I'll follow up when I do.

When I looked into this before our upgrade to GP 2018 last year, it looked like the Receivables Inquiry window would be more difficult to fix since it's not just calling a single stored procedure that we can tweak like the Payables window.
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.