Thursday, September 24, 2009

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.


Mariano Gomez said...

So, now what for Analytical Accounting? Any resolutions for AA performance? I have heard this to be a big issue across the board.

Mariano Gomez, MVP
Maximum Global Business, LLC

Steve Endow said...

Good question.

It makes you wonder what is going on behind the scenes with AA and SmartLists that is causing that much chaos. Seems like there is alot of room for improvement.

Fortunately I've only had to deal with AA once. It was a SOP integration with AA, and the AA portion was a nightmare, even with "supposed" eConnect support for AA transactions. It required an ugly workaround to get the data in properly.

And don't get me started with how AA stores its keys in the Dynamics database, making it impossible to restore databases at different points in time or in different environments for testing purposes. Or heaven forbid having to recover a production database without having a matching copy of Dynamics.

I just received another request for an AA integration, so I'm definitely going to reserve plenty of time for troubleshooting.