Open Forum

Like what you see? Discover the benefits of the GPUG Community. Learn More

AP Inquiry by Document GP2018 so slow

  • 1.  AP Inquiry by Document GP2018 so slow

    Posted 05-21-2018 06:47 PM
    ​Has anyone else been discouraged by the changes in GP2018 AP Iquiry by Document being so slow.  We use this feature all the time.  But we have history that dates back many years.  So instead of the window popping up int takes over 3 minutes for this window to pop up.  The reason we use over the vendor is some vendor are the same of very similar.  Example we have a bunch of FEDEX accounts.  To sit there and figure out what account it is for it is easier to just type in the inoivce # and search that way.  It would be nice if it would come up blank until you did an actual search.  Not bring up all the data first.  ​

    ------------------------------
    Eugene Mauermann
    Accounting Manager
    National Business Furniture, LLC
    Milwaukee WI
    ------------------------------


  • 2.  RE: AP Inquiry by Document GP2018 so slow

    Posted 05-22-2018 09:18 AM
    ​document look up is so slow, it's not worth it

    ------------------------------
    Barb Lockard
    Packaging Distribution Services, Inc.
    Des Moines IA
    ------------------------------



  • 3.  RE: AP Inquiry by Document GP2018 so slow

    TOP CONTRIBUTOR
    Posted 05-22-2018 09:25 AM
    ​Hi @Eugene Mauermann and @Barb Lockard

    If you guys are experiencing slowness or issues with AP Inquiry a couple of things could be going on.

    1) You may have some corrupted data in your AP tables, this could be in any of the table series of Work , Open or History.
    ​​          a)  If the slowdown in that significant then you will need to speak to your partners about running some scripts to look for issues.
              b)  Running an AP Reconcile (in AP utilities) and or Purchasing Reconcile's may also help significantly

    ------------------------------
    Kindest Regards,

    Jo deRuiter
    "That GP Red Head"
    Advanced Credentialed Professional-Dynamics GP
    Member, GPUG GP Credentialing Exam Committee
    Chairman, GPUG Partner Advisory Board
    "......what isn't she up to?"
    Heartland Business Systems, LLC
    Senior Financial Systems Consultant
    Milwaukee, WI
    770-906-4504 (Cell)
    ------------------------------



  • 4.  RE: AP Inquiry by Document GP2018 so slow

    TOP CONTRIBUTOR
    Posted 05-22-2018 09:28 AM
    ​I hit be button wrong - let me finish

    Additionally, slowness could be due to the fact that some third parties that had once kept formats or documents EXTERNAL to the gp tables are now accessed from within GP.   These third parties can leave the old network addresses within your tables and if you have decommissioned the location of those files and have not removed the address from your GP tables then GP will try and access those locations until it times out and then will move on to inquiry.

    You should have your partner search for those orphaned locations as well.

    These are just a couple of suggestions, but if you guys are experiencing that many issues you probably need to obtain some Partner-level help to resolve them.

    @Eugene Mauermann @Barb Lockard
    ​​

    ------------------------------
    Kindest Regards,

    Jo deRuiter
    "That GP Red Head"
    Advanced Credentialed Professional-Dynamics GP
    Member, GPUG GP Credentialing Exam Committee
    Chairman, GPUG Partner Advisory Board
    "......what isn't she up to?"
    Heartland Business Systems, LLC
    Senior Financial Systems Consultant
    Milwaukee, WI
    770-906-4504 (Cell)
    ------------------------------



  • 5.  RE: AP Inquiry by Document GP2018 so slow

    TOP CONTRIBUTOR
    Posted 05-22-2018 12:34 PM
    We have problems with the performance of this window also.
    Why is this window so slow for large data sets?
    The window wants to open with data, but it is looking up the data using only the document number, which can take up to three minutes if your data files are huge.
    If you do a SQL query by the document number on either the RM or PM history/open tables, the query runs many times faster if you also provide the document type, and also for the PM history/open tables, the vendor ID.
    It should be a simple Dex trigger script to abort the running of this initial window fill.
    But you would still have the problem that when you provide a document number that it would take three minutes to return the results.
    So if you have a large data set, probably the best solution is to pull the security to this window and force the users to use the vendor ID PM inquiry window, which is much quicker.
    You could also experiment with adding a non-unique key to the history file for only the document number in SQL.  (You would need to re-create this key each time you upgrade.)

    ------------------------------
    Bruce Strom
    Programmer Analyst
    Associated Grocers of Florida
    sunrise FL
    ------------------------------



  • 6.  RE: AP Inquiry by Document GP2018 so slow

    GPUG ALL STAR
    Posted 05-22-2018 11:14 AM
    Hi Eugene,
    Why don't you use a SmartList instead of the direct Inquiry ?  I agree that the design of the "Transaction by Document" form is bad because it taps into the integral part of your history, and if you have several hundreds of thousands of records, it can be long before it comes up..
    With a SmartList, you could achieve a much quicker result by filtering out the documents to return by a date range and / or Vendor ID..
    You'd still have the benefits of the direct access to the transactions with the GoTo's in SmartList.

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Ultra-Electronics Forensic Technology Inc.
    Montreal QC/Canada
    @GP_Beat http://dyngpbeat.wordpress.com/
    Montreal QC GPUG Chapter Leader
    GP2013R2 / MR2012 CU14
    ------------------------------



  • 7.  RE: AP Inquiry by Document GP2018 so slow

    SILVER CONTRIBUTOR
    Posted 05-23-2018 08:26 AM
    In native GP I've not seen that window slow. The developers generally do a pretty good job optimizing those things.

    I agree with all the above... if it were me, I'd run a SQL trace on the form opening and see what else is happening to slow it down.

    You might just need an index.

    ------------------------------
    Steve Gray [Designation]
    Vice President of Development
    941-747-3669
    DynDeveloper.com
    Training GP Developers at DynDeveloper.com
    Bradenton FL
    ------------------------------



  • 8.  RE: AP Inquiry by Document GP2018 so slow

    GOLD CONTRIBUTOR
    Posted 05-23-2018 11:27 AM
    Hi Eugene,

    I'm interested in understanding what is causing the slow performance of the AP Inquiry window in your environment and seeing if I can identify the cause and a potential solution. (no charge)

    I've been looking for real world situations where I can analyze GP performance issues and SQL query performance, and this sounds like a good candidate.

    I'll need to research how the window works and how it retrieves data on my test server, and then I would want to see how those operations behave in your environment.  It could be a SQL query that is performing poorly, or it could be poorly designed Dex that is looping through thousands of records.

    If you're interested in digging into it, please contact me at steveendow (at) gmail (dot) com.

    ------------------------------
    Steve Endow, Microsoft MVP
    Precipio Services
    Los Angeles, CA
    ------------------------------



  • 9.  RE: AP Inquiry by Document GP2018 so slow

    GOLD CONTRIBUTOR
    Posted 05-23-2018 03:18 PM
    Edited by Steve Endow 05-23-2018 03:21 PM
    I've taken a quick glance at the SQL that is fired by the "Payables Transaction Inquiry - Document" window when you enter a document number and click Refresh.

    It's not pretty.  I'm not a Dex developer, so I don't know if this is accepted practice for Dex, but the window is first retrieving a list of invoices with the given Doc Number, and is then calling multiple subsequent queries for each invoice that was found.

    So if you enter doc number "12345", it queries PM00400 to find all invoices with that doc num.

    Then, for each record retrieved from PM00400, it separately queries PM00200 to get vendor info for the invoice.  It then queries the PM transaction table (in my test it was PM20000) to retrieve basic info about the invoice (apparently via the zDP_PM20000SS_2 stored proc).  Once it gets those results, it then queries PM20000 yet AGAIN to retrieve full information about the invoice.  It does this for EVERY invoice that is displayed in the scrolling window.

    So if you have 100 documents with a doc number of 12345, it's performing over 300 separate SQL queries to display the resulting records in the inquiry window.  Obviously, this is horribly inefficient, and explains why it performs so poorly.

    Given what I'm seeing, I don't believe that any amount of SQL optimization would noticeably help this process in an environment with tons of AP transactions.  A new window would be required.

    I'll see if I can prototype a .NET window that would work more efficiently.  In theory, it should be possible to use a single SQL query to retrieve all of the necessary data.


    ------------------------------
    Steve Endow, Microsoft MVP
    Precipio Services
    Los Angeles, CA
    ------------------------------



  • 10.  RE: AP Inquiry by Document GP2018 so slow

    SILVER CONTRIBUTOR
    Posted 05-23-2018 03:44 PM
    I would like to note the following on this topic. This slowness on this window came about with some additional sorts added for GP 2018.  It was very slow with the initial release of 2018 and now they list it as fixed with the hotfix that came out.  In my opinion it can still be very slow to load even with the hotfix. I would suggest using a smartlist as a workaround.

    https://community.dynamics.com/gp/b/dynamicsgp/archive/2018/01/19/microsoft-dynamics-gp-january-2018-hotfix-has-released

    ------------------------------
    Donny Kensmoe
    Consultant
    Wipfli
    Green Bay WI
    ------------------------------



  • 11.  RE: AP Inquiry by Document GP2018 so slow

    TOP CONTRIBUTOR
    Posted 05-23-2018 03:46 PM
    @Donny Kensmoe

    That's some great information!!​

    ------------------------------
    Kindest Regards,

    Jo deRuiter
    "That GP Red Head"
    Advanced Credentialed Professional-Dynamics GP
    Member, GPUG GP Credentialing Exam Committee
    Chairman, GPUG Partner Advisory Board
    "......what isn't she up to?"
    Heartland Business Systems, LLC
    Senior Financial Systems Consultant
    Milwaukee, WI
    770-906-4504 (Cell)
    ------------------------------



  • 12.  RE: AP Inquiry by Document GP2018 so slow

    TOP CONTRIBUTOR
    Posted 05-24-2018 09:44 AM
    Steve,
    Try adding a non-unique key to the SQL table on just the document number, bypassing Dexterity.  That should do the trick.
    Bruce

    ------------------------------
    Bruce Strom
    Programmer Analyst
    Associated Grocers of Florida
    sunrise FL
    ------------------------------



  • 13.  RE: AP Inquiry by Document GP2018 so slow

    GPUG ALL STAR
    Posted 05-24-2018 11:50 AM
    Edited by Beat Bucher 05-24-2018 11:50 AM
    Steve,
    I'm not (very) surprised by your findings.. I've traced other stored procedures (zdp_xxx) in GP and found some curious ways of querying SQL, by repeating sometimes 3-4 times the same query with the same parameter.. like the developer just couldn't trust the data or wanted to populate some other temp table.. but all in all a lot of bandwidth & CPU wasted for nothing.

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Ultra-Electronics Forensic Technology Inc.
    Montreal QC/Canada
    @GP_Beat http://dyngpbeat.wordpress.com/
    Montreal QC GPUG Chapter Leader
    GP2013R2 / MR2012 CU14
    ------------------------------



  • 14.  RE: AP Inquiry by Document GP2018 so slow

    GOLD CONTRIBUTOR
    Posted 05-24-2018 10:50 PM
    Edited by Steve Endow 05-24-2018 11:31 PM
    I have created a prototype of a "PM Transaction Search" window that can replace the two PM Transaction inquiry windows and provides a much better tool for AP transaction searches.

    Here is a demo video:

    https://www.youtube.com/watch?v=yBy449joHwc


    It needs to be tested in a proper test environment with more data to see how it performs, but the underlying stored proc could be adjusted if necessary, and I can add an option to only search when Enter is pressed, rather than searching after each key is typed.

    ------------------------------
    Steve Endow, Microsoft MVP
    Precipio Services
    Los Angeles, CA
    ------------------------------



  • 15.  RE: AP Inquiry by Document GP2018 so slow

    Posted 05-24-2018 11:31 PM
    When I grow up, I want to be like @Steve Endow

    ------------------------------
    Amber Bell
    Training Dynamo LLC
    Email: amber@trainingdynamo.com
    Website: www.trainingdynamo.com
    Twitter: @trainingdynamo
    ------------------------------



  • 16.  RE: AP Inquiry by Document GP2018 so slow

    GOLD CONTRIBUTOR
    Posted 06-05-2018 11:30 PM
    Based on this forum thread, I have developed and released version 1.0 of the "GP Transaction Search", which is available for FREE.

    Version 1.0 includes a PM Transaction Search window.  The window allows you to search for PM transactions by date range, vendor ID, vendor name, document number, and amount range--all at the same time.  It has drill down capability to view the transactions directly in GP zoom windows, and lets you copy the date and export to CSV file.

    It was tested on a large Dynamics GP company database, and once the stored procedure was run a few times, it ran consistently in well under 1 second.

    Installation is extremely simple and should take less than 5 minutes.

    Here is my blog post with more information and the download link:

    https://dynamicsgpland.blogspot.com/2018/06/dynamics-gp-transaction-search-v10-is.html

    Here is the video overview:

    https://youtu.be/R8nvAol7mPA


    I would like to publish the code as an open source project so that other developers can contribute, such as adding search windows for other modules.

    If you have any questions, or if you have a chance to try the search tool and have feedback, I'd love to hear what you think.

    ------------------------------
    Steve Endow, Microsoft MVP
    Precipio Services
    Los Angeles, CA
    steveendow (at) gmail (dot) com
    ------------------------------



  • 17.  RE: AP Inquiry by Document GP2018 so slow

    GOLD CONTRIBUTOR
    Posted 06-06-2018 08:42 PM
    For any GP .NET developers that are interested, I just published the GP Transaction Search project as an open source project on GitHub.

    https://github.com/steveendow/gp-transaction-search


    It's the first time I've published on GitHub and this is the first open source project I've done, so I'm open to suggestions, pointers, help, etc.

    ------------------------------
    Steve Endow, Microsoft MVP
    Precipio Services
    Los Angeles, CA
    steveendow (at) gmail (dot) com
    ------------------------------



  • 18.  RE: AP Inquiry by Document GP2018 so slow

    GOLD CONTRIBUTOR
    Posted 07-05-2018 04:01 PM
    Version 2.0 of the FREE Dynamics GP Transaction Search is now available!

    This new version adds RM Transaction Search and SOP Transaction Search windows to the original PM search window.

    This new version also uses the Winthrop Visual Studio Integration Toolkit to provide custom Dynamics GP menus for the search windows.

    A big thanks to Ian Grieve for assistance with the development and testing of this new version!

    Here is a blog post and video of the new version:

    https://dynamicsgpland.blogspot.com/2018/07/free-gp-transaction-search-v20-now.html


    For more information and to download compiled releases:



    ------------------------------
    Steve Endow, Microsoft MVP
    Precipio Services
    Los Angeles, CA
    steveendow (at) gmail (dot) com
    ------------------------------



  • 19.  RE: AP Inquiry by Document GP2018 so slow

    TOP CONTRIBUTOR
    Posted 07-06-2018 10:02 AM
    @Steve Endow -- you are awesome! Thanks for this!

    Joe​

    ------------------------------
    Joseph Markovich
    IT Manager
    Okaya Electric America
    Valparaiso IN
    ------------------------------