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.
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.
-- Enjoy the Life+-+-+-+-+-+-+-+ Beat BUCHER Dynamics GP 2010R2 Business Analystwww.fti-ibis.com +-+-+-+-+-+-+-+
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.
I can do that. Any particular trace tool you recommend? I"m not a SQL guru.
Paul, thank you and I'll give that a try. I figure if Smart List doesnt work then something else can be tried.
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.
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.
Richard L. Whaley Author, Publisher, Consultant
Enhancing your Dynamics Knowledge!
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.
Leslie Vail, CPA, MVP, MCT, MCITP, MCP, MCITSASCI, Inc. * PO Box 600965 * Dallas, TX 75360 * 972-814-8550 * firstname.lastname@example.org
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.
Other Microsoft Sites
I'm a Customer
I'm a Partner
Use the official Twitter tags:
#MSDYNCOMM | #CONV13