75,730
Clients has been running Smartlists for years. They have accumulated over 1,000,000 records in the SOP30300 table. Running the unfiltered sales line detail Smartlist will take over 3 minutes before any records are displayed. If I go to SSMS and run SELECT TOP 1000 FROM [dbo].[SOP30300] the records display almost immediately. What could be causing this difference in GP with the Smartlist?
To add on, SmartList wasn't designed to handle very complex reporting nor filter through a million records to find the records to display and usually, when we start getting higher than about 1000 to 3000 records returned, depending on the environment, we start to see issues with the default SmartList functionality.
For more complex reports, we recommend SSRS or Excel reports, which can handle heavier processing and filtering and return larger amounts of records without issues.
SmartList Builder may have their own pros/cons of that as well.
Thanks
Yes, if they own SL Builder, I'd definitely get them create a custom View with some filters applied upfront to now grab too much data.. unless someone wants to dive back into historical data, there should be no reason to retrieve any other data that is more than 2-3 years old.
SmartList should be used for current FY data.. not historical data. For that purpose you setup SSRS or Excel reports for the customer.
So we have dumb Smartlists. That is good to know. Now they do have Smartlist Builder so I can create a new view and have them use that.
Hi Richard,
Unfortunately SmartList isn't as Smart as it sounds.. The way the queries are called to the back-end will likely show that they are far from optimal when sent to SQL.
Are those built-in SL's from GP or custom-made with SL Designer or SL Builder ?
What about SQL views to optimize the query and not dig into the Millions of rows from SOP30300..
By default, the GP built-in SL's are using the built-in SQL view to retrieve data from GP, i.e. SalesTransactions and SalesLineItems... Those views were created by Microsoft to return combined data from Open, Work & History tables..
HTH
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (