Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Smart reports take 30 ...
Microsoft Dynamics GP (Archived)

Smart reports take 30 minutes to complete

(0) ShareShare
ReportReport
Posted on by Tom @ HNNCSB 900

Hello,

 We are running GP 10 here, up to SP 4.  Users are reporting smart list reports take 20 minutes or longer.  I ran a simple report that our payroll staff uses and it took 30 minutes to complete, returning only two lines. Normal use of the GP client is not otherwise slow.

 Looking at the database server before, during, and after the reports are run, I see no changes in CPU, memory, or network utilization (all very low on this server).

 We host the GP client on XenApp (Citrix Presentation Server).  On those XenApp servers, I also see no utilization issues.

Anyone have any suggestions?  I really can't tell if this is commuication, database, or perhaps some optimization issue.  I do already run a number of nightly/weekly routines on the databases for optimization.

 Suggestions appreciated!

Tom

*This post is locked for comments

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    Re: Re: Re: Smart reports take 30 minutes to complete

    Hey Richard and Leslie,

    Thanks for that tip too... I wasn't aware that adding an index to one or two of the tables used by SmartList could make such a difference... I always tried to use an alternative approach if the query was taking too much time to run (i.e. Excel or external reporting tools).

    For all those who want to stick with SmartList exports to Excel, I used in the past an add-on called EZ-GP (can be found at http://www.datapresentation.com/products/EZ-GP.cfm ) which fixed even a broken export functionality for Excel (i.e. when the OWC libs where missing or not working at all, due to security rights in the registry). The add-on can export 10'000 records in 10 sec... The product is very cheap for what it does and they produce also a variety of other Excel companion products.

     

  • L Vail Profile Picture
    L Vail 65,271 on at
    Re: Re: Smart reports take 30 minutes to complete

    I agree with Richard.

    You probably need to look at that query being used to populate the SmartList. Using indexed fields makes a HUGE difference in how long it takes to execute the query. If need be, you could add an index to the table without causing the Dynamics program to fail. You can't add a field, but an index does not change the record length. Be sure to script it if this is the path that makes sense, because it will be wiped out if an upgrade changes that table.

    Kind regards,

    Leslie

  • Richard Whaley Profile Picture
    Richard Whaley 25,195 on at
    Re: Smart reports take 30 minutes to complete

    Forget all the tracing suggestions, here is what you will find.  Your SmartList is sorting and / or selecting on a non-keyed field.  If necessary create a supplimental index on the tables using the selection fields of your SmartList.  Alternately, you can re-write the SmartList using indexed fields. 

    Using the first method I took a SmartList that was running 10-20 minutes and reduced the run time to 3 seconds.

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    Re: Re: Re: Smart reports take 30 minutes to complete

    Tom,

    I wouldn't waste too much time with tracing... specially if you're not comfortable with SQL Studio management and tracing tools.

    Rather take the opposite approach : the Expert Exchange advise might be a good idea, i.e. build a new report with Excel, SSRS or Crystal. You said the smartlist was created with SL Builder, thus you should be able to edit the source query and find out what tables/resources it was using and what filtering is applied that might cause the long execution time. I built quite a few reports for the HR module related with Paycodes, and may provide you with some table definitions.

  • Tom @ HNNCSB Profile Picture
    Tom @ HNNCSB 900 on at
    Re: Re: Re: Re: Re: Re: Smart reports take 30 minutes to complete

    Paul, thank you and I'll give that a try.  I figure if Smart List doesnt work then something else can be tried. 

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Re: Re: Re: Re: Smart reports take 30 minutes to complete
    On the SQL Box, Start the SQL Management Console, On the top line, choose tools then sql profiler. Once the profiler starts, on the fop like choose file then new trace. Connect to the sql server running Dynamics. Click on the Events selection Tab at the top, then click on the Column Filters Button. Select the LoginName from the left hand side of the screen. Then click on the + button next to 'like' on the right hand side and enter the HR Username that is running the report. Click ok and then Click Run and let the user run their report. you can then compare the start and end time for each sql statement they issue together with how many DB Reads they are issuing. Hope this helps. Sorry about the formatting but I have not worked out how to put paragraphs in here yet.
  • Tom @ HNNCSB Profile Picture
    Tom @ HNNCSB 900 on at
    Re: Re: Re: Re: Smart reports take 30 minutes to complete

    I can do that.  Any particular trace tool you recommend?  I"m not a SQL guru. 

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Re: Re: Smart reports take 30 minutes to complete
    one way to check is to get a profiler trace running on your sql box you can set it up to only return items for your hr user and that will then tell you if the query that the smartlist is issuing is having problems.
  • Tom @ HNNCSB Profile Picture
    Tom @ HNNCSB 900 on at
    Re: Re: Smart reports take 30 minutes to complete

    It is some made.  This simple query just looks for particular paycode during a certain check date.  I think it was created with SmartList builder (I didn't create it). 

    I found another post on Experts Exchange where is could be just the SmartList is the wrong tool as it has to looks at many records to find the data.  Other tools such as Excel Report Builder or SQL Reporting services were suggested as better options.

    Thoughts?  I guess I am wondering why have SmartList if it's so slow in some circumstances.

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    Re: Smart reports take 30 minutes to complete

    Hi Tom,

    Is the smart-list HR tries to run a built-in from GP or home-made ? maybe it was built in the wrong way and loops on itself...  Do you use SmartList builder ? this is an add-on tool for GP that allows to create more complexe queries with 3rd party tables that are not supported by GP out-of-the-box.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans