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
WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
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.