Optimize SmartList Performance in Microsoft Dynamics GP

SmartList Usage

SmartList and SmartList Builder are intended to be used as a query tool only.  If you decide to increase the maximum record count, be aware that query processing will be slow and you may experience errors.  Therefore, we recommend that you use the default record count limit of 1,000 when you execute queries.

SmartList Search Criteria

SmartList allows vast amounts of searching options that users can apply to the SmartList query data. With this flexibility comes the potential for very high I/O costs if the query / database design was not optimized for a specific search criterion. Review the suggestions below to reduce I/O cost when designing SmartList search criteria:

·      Avoid using the "is not equal to" filter

·      Use the "equal to" instead of "contains" filter

·      If the Smartlist includes WORK, OPEN and HISTORY tables and the user is only interested in WORK data, use the "Document Status" or similar search criteria to skip searching OPEN and HISTORY tables.

Default SmartList Objects vs SmartList Builder Objects

Default SmartLists may not always be the best option as the query is generic.  Given the amount of flexibility and amount of data to search it may be better to utilize a custom SmartList Builder that is optimized for the necessary results. 

SmartLists with Extender Data Added

Adding Extender fields from an Extender Window, Form, or Detail Form can cause a SmartList to return data slower.  The linking required to include the Extender data adds complexity to the SmartList.  It may be beneficial to look at using SmartList Builder to build a custom SmartList that includes the Extender data to optimize the SmartList.

Export of SmartList Data to Microsoft Excel
The export of SmartList data to Excel can take some time depending on the amount of data you are trying to export.  The number of columns included in the SmartList as well as the number of rows can influence the export performance.  A SmartList exports to Excel field by field to ensure the formatting is also brought across with the data.  Therefore, the export can take longer for all of the data to appear in the Excel spreadsheet.  If you are running SmartLists for the purpose of exporting the data to an Excel spreadsheet, you may want to consider the default Excel Reports that can be deployed in Microsoft Dynamics GP.  They are setup very similar to the default SmartLists, however the data is directly pulled into Excel so that you do not have to export from SmartList.  The additional benefit to this is that the data is refreshable in Excel so you do not have to continually export the data from SmartList.