Open Forum

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

PO Smartlist - Performance too slow

  • 1.  PO Smartlist - Performance too slow

    TOP CONTRIBUTOR
    Posted 03-13-2018 11:08 AM
    Hi There,

    We have few smartlist that are related to Purchase orders, Performance of having results to display on screen from that smartlist is way too slow.
    Is there anything I could do to improve the browsing of these smartlist?

    Thanks!
    Sunaina  ​

    ------------------------------
    Sunaina Sharma, Business Application Analyst(Admin)
    Oakville Enterprise Corporation
    ------------------------------


  • 2.  RE: PO Smartlist - Performance too slow

    TOP CONTRIBUTOR
    Posted 03-13-2018 11:21 AM

    ​Hi @Sunaina Sharma


    It sounds like you guys may have a tremendous amount of data.  Even if you limit the data with filters the PO SmartLists will run slowly.


    If you need to keep them in SmartLists someone else on here might have a solution, but I'm going to recommend you use Refreshable Excel Reports - the tend to run much faster for things such as the PO information.

    There's quite a few articles out there on how to set them up.  But, in a nutshell you'll either write the query directly in an Excel SQL connection or write a view and deploy it and then query the view from within Excel.

    This can dramatically increase the speed.

    With this said, the slowness could be due to a number of issues from space in SQL, space on the Server to the network connection joining your workstation or even the CPU of your workstation.   There are too many varied configurations to troubleshoot that from the information you've provided.


    ------------------------------
    Regards,

    Jo deRuiter
    "That GP Red Head"
    Senior Financial Systems Consultant
    Heartland Business Systems, LLC
    Milwaukee, WI
    770-906-4504 (Cell)
    ------------------------------



  • 3.  RE: PO Smartlist - Performance too slow

    GPUG ALL STAR
    Posted 03-14-2018 07:53 AM
    HI @Sunaina Sharma,

    As Jo suggested, Excel refreshable reports would be a much speedier alternative.. That being said, It sounds strange that your SL report takes so long to display the PO data.. I'm wondering, are you archiving your closed PO's on a regular basis..? this is one of the factors that can lead to slow display of the data..
    Completed PO's are not automatically moved to historical tables, which may be the cause for your slowness.. you need to run this routine from time to time :
    However, it could also be the query that you use to retrieve your PO's.. is it a default SL report or some home-brewed SL ? Some of the default SL's are based on SQL views that retrieve both the historical & current data.. which in the case of large amount of PO's can take more time. The last question is also about how many records are your returning in your SL's ? by default, GP returns 1000 records, but you can change that and save it with the SL.. in some cases if someone changed the default from 1000 to 10000, that could take significantly more time.. ​

    ------------------------------
    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
    ------------------------------



  • 4.  RE: PO Smartlist - Performance too slow

    TOP CONTRIBUTOR
    Posted 03-14-2018 10:05 AM
    The problem with many Smartlists is that they want to bring in the entire table's or query's worth of data so that subsequent sorts run faster.  It really defeats the purpose when there is lots of data in the first place!  A couple of suggestions:

    1.  Use an SSRS report instead of a Smartlist.  The SQL Server database engine can do a much better job of getting and retrieving information than the Dynamics platform can.  Use it.  You can publish all the Dynamics GP reports as SSRS reports.  Then have someone (preferably on your team) tweak the published report definition to give you what you need and put in better filters (date range, etc.).

    2.  Create a view-based Smartlist with an inherent date filter (last year, etc.) and build your Smartlist off that.

    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise ID
    ------------------------------



  • 5.  RE: PO Smartlist - Performance too slow

    Posted 03-14-2018 12:25 PM
    Edited by Gary Siebenlist 03-14-2018 12:34 PM

    Hi Sunaina,

    Have you tried adding the following line to your DEX.INI settings file?

    SmartlistEnhancedExcelExport=TRUE

    This setting uses a different method to export data to Excel. It's much faster, but certain things do not export correctly, some foreign currency symbols for one. Also it strips off leading zeros. Test it first before deploying.

    It made a significant improvement in speed for exporting Smartlists to Excel from my experience. 



    ------------------------------
    Gary Siebenlist
    Sempra Infrastructure
    San Diego CA
    ------------------------------



  • 6.  RE: PO Smartlist - Performance too slow

    SILVER CONTRIBUTOR
    Posted 03-14-2018 03:31 PM
    ​If you are using large data sets Smartlist has performance issues as it works thru Dex and older Excel models.    Additionally it doesn't aggregate so you have to bring in every line of data.

    Another option is BI360 Report Composer which has built in Dynamics GP Metadata and allows groupings and calculations.

    There is a Free BI360 Report Composer Offer.

    https://www.solverglobal.com/campaign/composeroffer
    Solverglobal remove preview
    BI360 is enabling world-class decisions one user at a time. In the spirit of spreading the word and giving you the tools you need, we are giving away 5,000 team licenses (up to 20 users each) as of September 18, 2017!
    View this on Solverglobal >


    ------------------------------
    Jim Hummer
    Consultant
    Solver
    Los Angeles CA
    ------------------------------