web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
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

I have the same question (0)
  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at

    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.

  • Tom @ HNNCSB Profile Picture
    Tom @ HNNCSB 900 on at

    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.

  • Community Member Profile Picture
    Community Member on at
    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

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

  • Community Member Profile Picture
    Community Member on at
    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

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

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at

    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.

  • Richard Whaley Profile Picture
    Richard Whaley 25,195 on at

    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.

  • L Vail Profile Picture
    L Vail 65,271 on at

    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

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at

    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.

     

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the March Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans