Performance delays when you refresh shippers and sales orders in the OM Documents screen

This article provides a workaround for performance delays that occur when you refresh shippers and sales orders in the OM Documents screen.

Applies to:   Microsoft Dynamics SL
Original KB number:   2950005

Symptoms

Consider the following scenario in Order Management in Microsoft Dynamics SL:

In OM Documents (10.225.00), you experience poor performance when you refresh shippers and sales orders.

Note

This problem can occur when you have a database that has many sales orders and shippers.

Resolution

To work around this issue, change the stored procedures and add the additional indexes that are listed in the More information section.

More information

To make the necessary changes to resolve this issue, follow these steps:

1. Add (NOLOCK) to all tables in the following stored procedures:

   • WOSOShipLine_InvtID_filter
   • WOSOSched_InvtID_filter
   • POAlloc_CpnyID_SOON_SOL_nolike

2. Add the following indexes:

   CREATE NONCLUSTERED INDEX [xSOShipHeader_OrdDate] ON [dbo].[SOShipHeader]  
   (  
   OrdDate] ASC  
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,  
   IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,  
   ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   

   CREATE NONCLUSTERED INDEX [xSOShipHeader_ShipDateAct] ON [dbo].[SOShipHeader]  
   (  
   [ShipDateAct] ASC  
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,  
   IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,  
   ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   

   CREATE NONCLUSTERED INDEX [xSOHeader2] ON [dbo].[SOHeader]  
   (  
   [CpnyID] ASC,  
   [SOTypeID] ASC,  
   [CustID] ASC,  
   [OrdDate] ASC,  
   [BuildAvailDate] ASC,  
   [BuildInvtID] ASC,  
   [BuildQty] ASC,  
   [BuildSiteID] ASC,  
   [CustOrdNbr] ASC,  
   [OrdNbr] ASC,  
   [Status] ASC  
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,  
   IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,  
   ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]