Forums

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.

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.

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.

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

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.

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

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.

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.

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

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.