We are seeing 1-4 Hour Smartlist report times but base SQL Runs in seconds

(1) Share

Report

Posted on by Community Member

On our HITB Inventory View & HITB Report by GL Smarlistst we are experiencing hour long to multi-hour response time as the windows paints each row by row for 29K rows to return the result set. If I extract the base sql or run the underlying view from our SQL Server SSMS both of these run in 1-2 seconds. We are running Dynamics GP 2013 with 20-25 active users on the system typically.

Dynamics GP 12.00.1801 (r2)

Smartlist 12.00.1722

System Windows Server 2008 R2

ODBC Driver Manager 03.80.0000

ODB Driver 10.00.1600

MS SQL Server 2012(SP1) 11.0.31565 (x64)

While it used to be slow around Month End this is orders of magnitude slower.

Very interested to hear if anyone else has hit this and if so how did you correct?

Thank you! Andre

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

Lucas Miller on at

Like (1)

Report

Hello Andre,

Returning 29,000 records in SmartList may be possible in some instances/environments, but it is quite a ways beyond what it was designed for as a quick query tool. We typically tell people to limit results to less than 5000 records for best performance.

When I get questions on issues like this the one thing that tends to lead to the best user experience is to switch to a refreshable Excel report to return this sort of data. Since you're on Dynamics GP 2013 you could have used SmartList Builder to create these SmartLists, which means you have the ability to quickly copy them to Excel Report Builder and deploy them from there. Excel running a direct query against the database is going to perform much closer to you querying the view in SSMS than trying to return that many records in SmartList.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for the reply & I’ll have a look at the Excel Report Builder having said that seems like gotten a lot worse since we applied last couple month’s of Microsoft patch Tuesday updates.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Howdy Lucas -

first, wow have we even been (mis)using Smartlists, thank you but am stuck.

working on migrating to Excel Report Builder but stuck on what should be obvious but not. When I go to publish I get the error - "there are columns in the report with the same display name..." but I've gone through the list, had admin go through the list and there are no dup display names. Have to be missing something but been through the list multiple times and still don't see it.

It will let me Options>>Preview the data which looks good and extremely fast.

Thank you! Andre

Was this reply helpful? Yes No
Verified answer

Lucas Miller on at

Like (1)

Report

Andre,

I haven't seen any reports of issues with a Windows patch, but you could try removing the patch to see if that improves performance.

Regarding the Excel Report Builder error, it's probably been 5 years since I've looked at that product before it was taken over by eOne, so I don't recall any such error. I guess I would recommend capturing a SQL Profiler trace while you recreate the error to see if you can get more information about why the error is being generated. I suspect it is going to be during the creation of the SQL view you're specified. There are probably going to be alias names used for the columns in the view and that sounds like what is being duplicated.

Was this reply helpful? Yes No
KirkLivermont 5,985 on at

Like (0)

Report

Andre,

It sounds like this particular thread is resolved but my guess it is the Spectre/Meltdown patches which caused the performance issues. Disabling speculative execution will negatively impact performance although is necessary from a security standpoint.

Regards,

Kirk

Was this reply helpful? Yes No