Sleuthing Scandalously Slow SmartLists

I recently received an inquiry from a friend at BlueLock. BlueLock provides infrastructure hosting as a service. Don't want to deal with servers? Need to have a virtual infrastructure that allows offices in Tokyo, Paris, Moscow, London, and New York to all easily access your Dynamics GP system? That's what they do--all very fancy cutting edge hosting and virtualization, with enterprise grade Citrix and VMWare hosting.

So BlueLock is hosting a 25 user GP system for an international company with offices around the globe, and they are looking to ensure that the performance will be sufficient. GP is working fine, but there is one strange issue: Some SmartLists take forever to run. A Sales Transaction SmartList might take 20 minutes to output 15,000 records. A GL Account list would take 5 minutes to display 10,000 records. But when the equivalent SQL query is run directly on the SQL Server, the query runs in a second or two.

When I timed a few SmartLists on a physical SQL Server, my results were much, much faster than BlueLock's test times. Additionally, when BlueLock ran certain SmartLists, the server IO and CPU utilization would shoot through the roof, and sometimes GP would stop responding.

We then became concerned that perhaps there was some obscure issue with SmartLists in a fully virtualized environment.

After some head scratching, the BlueLock team looked again at the SQL Profiler trace results for one of the SmartLists. Instead of a single query, SmartLists was issuing many different queries, and even calling stored procedures. Reviewing the additional queries and stored procedures, they found that they all related to Analytical Accounting (AA).

They then tested two scenarios:

1) Remove any columns in the SmartList that relate to AA
2) Remove the AA module so that it isn't loaded when GP starts

Apparently removing the AA columns from the SmartList only had a minor effect on performance. However, removing AA rom the Dynamics.set file resulted in a huge improvement.

Here are two stats they recorded:

SmartList with AA columns removed = 10K records in ~20 minutes.
SmartList with AA removed from Dynamics.set file = 10K records in ~25-30 seconds.

So, by removing Analytical Accounting from the mix, the SmartList went from 20 minutes to 25 seconds.

That's quite an improvement.