Over time the amount of data stored by your Microsoft Dynamics GP system can get quite large. Without buying new hardware, there is a simple way to make your system faster and decrease the space used to store your databases.

This is where a couple of the features of the Database Module of GP Power Tools come in to help. This is article 3 of 3 in the series.

We need to start with a little theory first to explain the problems and then we can see how GP Power Tools solved them.

The Theory – Select Top Performance

There is a known issue with the SELECT TOP command in SQL Server where a query using a TOP clause will perform well most of the time but can suddenly and unexpectedly perform 10 to 20 times slower.

TLDR: The cause of the performance loss is SQL Server deciding to use the TEMP database while getting the first X records based on the order or index being used rather than using memory to complete the processing. Obviously completing this processing in memory is going to much faster than using storage, even if the TEMP database is stored on an SSD.

The article below explains the cause and a “trick” to ensure the processing stays in memory:

• Trick to Optimize TOP clause in SQL Server

The Problem – Select Top Performance

When Great Plains Dynamics version 3.10 and the underlying Dexterity development environment were first updated to allow the use of Microsoft SQL Server 6.0 as a storage option, the performance was significantly slower than expected and caused the SQL support to be delayed.

After some analysis the cause for the bad performance was the parsing of the text queries into process instructions for SQL to perform. As Dexterity already knows the table definitions (including indexes), it was realized that the set of select queries for a table were always the same and could be written into pre-compiled stored procedures. These “zDP_” stored procedures are called first and Dexterity will only fall back to the text queries if the stored procedure is not found.

When version 3.15 was released with support for SQL Server 6.0, the zDP stored procedures (AKA Auto Procedures) provided a 300% performance improvement. In a modern SQL Server environment, the performance loss from a missing Auto Procedure is not as significant, but if you drop all the zDP stored procedures from a Test Database, you will notice the slow down.

The Auto Procedures for select statements usually start with SELECT TOP 25 and so is susceptible to this known SELECT TOP performance issue. I learnt about this issue when a partner contacted me and asked if I had heard about it. After learning that there was an issue and finding a trick/hack/workaround solution, I decided to write a fix in GP Power Tools.

Note: The number 25 is used so that the system will retrieve up to 25 records at a time. This number can be changed with a Dex.ini setting, but 25 is a good compromise allowing access to single records but also allowing for multiple records for lookups and transaction lines, or when processing multiple records at a time. If you set the value too high, you have fewer queries sent to the server, but more network usage (large datasets). If you set the value too low, you have more queries sent to server and more network usage (more small datasets). It is recommended NOT to add or change the SQLCursorBlockSize Dex.ini setting from the default value of 25.

The Solution – Select Top Performance

The solution to this issue is the second of the features in GP Power Tools Database Tools Module, Auto Procedure Optimizer. This tool will allow you to update the SELECT TOP commands in the Auto Procedures to apply or un-apply the fix for the performance issue.

The Auto Procedure Optimizer window will display tables (with records) and allow you to select them for processing. When processed the Auto Procedures will be recreated to ensure they are valid and up to date for the table and then altered to apply the fix.

While this tool will not make your system faster, it will prevent unexpected slowdowns caused by this known issue.

More Information

For more information see the following articles

• #GPPT #MSDynGP Making your Dynamics GP system faster and decreasing data space Part 1
• #GPPT #MSDynGP Making your Dynamics GP system faster and decreasing data space Part 2
• #GPPT #MSDynGP Making your Dynamics GP system faster and decreasing data space Part 3
• #GPPT GP Power Tools: What’s in the Database Tools module?
• GP Power Tools Portal: https://winthropdc.com/GPPT
• Trick to Optimize TOP clause in SQL Server

Hope you find this information useful.

David

This article was originally posted on http://www.winthropdc.com/blog.