Displaying Data "Faster"

(0) Share

Report

Posted on by Community Member

Hello:

We have eighteen "active" companies within GP 2013 R2, and we represent a multicurrency environment.

I created a SmartList in SmartList Builder that is based on a view, and this view pulls data from three other views that I created.

Pulling data from this view is taking an enormous amount of time in SmartList.

Would pulling a view in SQL Reporting Services be any faster, or no?

Is there a third-party product that can be utilized?

In the past, I would compensate for this by creating a refreshable table in SQL based on a stored procedure rather than a view and have SmartList Builder pull from that table. But, I don't have immediate access to SQL. It's a long story. :)

Thanks! Much appreciated!

John

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

Verified answer

Shahid.Ahmed 170 on at

Like (0)

Report

Hi John,

You try accessing tables directly rather than other views. I would suggest you try to write your queries against the tables directly. It would optimize the performance.

Regards,

Shahid

Was this reply helpful? Yes No
MattPaulen 6,912 on at

Like (2)

Report

What about just pulling the data directly in to Excel through a data connection? Since most of the time that's what users end up dumping Smartlist into, by starting in Excel it cuts down on the time to export for sure.

From experience, Excel seems to be faster than Smartlist as well just in terms of pulling the data.

Was this reply helpful? Yes No
Suggested answer

Victoria Yudin 22,769 on at

Like (3)

Report

Both Shahid's and Matt's suggestions are right on. The problem is going to be you not having direct access to SQL. You really need that to address this issue. Once you have access to SQL, I would combine these suggestions:

Create a view or stored procedure that pulls in ONLY the data you want, directly from tables. In my experience, re-using views created for other reasons results in a lot of unneeded fields which take away from optimal performance. Also chaining views together typically degrades performance. If you're querying against a lot of data and users typically only need a subset of the data, a stored procedure with parameters is the way to go. Set up your view or stored procedure to run in Excel or SSRS. That will give you the best performance. If users are going to be dumping data to Excel anyway, might as well set it up in Excel, as Matt says.

Also, test directly in SQL to see how long it takes. If running in SQL directly take a long time, then you need to optimize your code, add indexes, etc. If it's fast in SQL, then Excel or SSRS should not add much overhead.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you, for this excellent tip, Matt!

John

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Brilliant analysis! Thanks, Victoria!

John

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

Thanks, Shahid!

I did exactly what you suggested! I joined directly to the tables. Speed has improved tremendously!

John

Was this reply helpful? Yes No