Performance delays when refreshing shippers and sales orders in the OM Document Screen

  • Article

This article provides a resolution to solve the performance delays issue when refreshing shippers and sales orders in the OM Document Screen in Microsoft Dynamics SL.

Applies to:   Microsoft Dynamics SL
Original KB number:   2953192

Cause

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

Resolution

Change the stored procedures and add the additional indexes listed here.

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

    WOSOShipLine_InvtID_filter
    WOSOSched_InvtID_filter
    POAlloc_CpnyID_SOON_SOL_nolike

  2. Add the following three 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]