Table Index Optimization to Improve Dynamics GP Performance

I recently touched up some code I wrote for a client back in 2003. For them, Bob and I integrated GP Depot Management Work Order Entry/Update with Sales Order Processing (Document Number = Work Order Number) for quoting and invoicing repair work and after sales service. Part of this customization enabled them to inquire on their quotes and orders directly from Work Order Entry/Update.

The code simply queries the Sales Order Processing Work (SOP10100) and Sales Order Processing History (SOP30200) tables where the ORIGNUMB = Work Order Number and then opens the Sales Order Processing Document Inquiry, sets the From and To Document Number, and sets the Unposted or History radio button accordingly. This saves the service center personnel significant time when researching work order detail for customers which increases customer satisfaction.

After 6+ years they've accumulated a little history in SOP. The code behind the inquiry buttons placed on Work Order Entry/Update window was taking too long to run. With customer service agents on the phone with customers, waiting more than a few seconds for the system to return data was too long. It didn't take long to realize that there wasn't an index on the SOP30200.ORIGNUMB column. The impact of adding that index was phenomenal reducing wait time from 10+ seconds to what seems like just milliseconds.

To create a new index in SQL Server Management Studio:

1. Right Click on the table and click Design from the menu.
2. From the Table Design menu option select Indexes/Keys.
3. Click Add to create a new index.

4. Select the column(s) on which you want to create the index.

5. Name your indexes consistently so that you can query all them out of sysobjects later when you need to.

6. Set other properties as needed.

There is a lot of great information widely available about when and how to create new indexes. I strongly recommend you educate yourself and engage an expert to optimize your indexes.

Of course, you can use T-SQL to load new indexes on your tables. Here's a sample:

CREATE NONCLUSTERED INDEX IX_SOP30200_ORIGNUMB ON dbo.SOP30200
(
ORIGNUMB
)

WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Remember that when you upgrade GP, it's likely that you will need to reload your indexes as the upgrade process will drop the old tables and therefore your indexes. Plan your upgrades accordingly and make sure you script your indexes before you do. Otherwise, GP will generally react positively to the new indexes.

http://mbsguru.blogspot.com/2009/04/table-index-optimization-to-improve.html