Summary:

Selecting the New Line Item button on a quote, where CRM is using a large number of products (100,000+) may result in a timeout.

Symptoms:

The user may experience a SQL timeout, or the screen may render, eventually.

Cause:

There are two separate issues here. Typically, a CRM install with an extreme number of products and associated prices will need custom indexes to perform well. These custom indexes can be added as follows:

CREATE NONCLUSTERED INDEX [IDX_CUSTOM_Parm_name] ON [dbo].[Custom_SysParams]
(
       [Parm_Name] ASC
)
INCLUDE (     [Parm_Value])
WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
       DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO


CREATE NONCLUSTERED INDEX [IDX_CUSTOM_Pric_priceCID_PricingListId_Active] ON [dbo].[Pricing]
(
       [pric_price_CID] ASC,
       [pric_PricingListID] ASC,
       [pric_Active] ASC,
       [pric_ProductID] ASC
)
INCLUDE (     [pric_UOMID])
WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
       DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
       FILLFACTOR = 95)
GO

CREATE NONCLUSTERED INDEX [IDX_CUSTOM_Prod_Active] ON [dbo].[NewProduct]
(
       [prod_Active] ASC
)
INCLUDE (     [Prod_ProductID],
       [prod_UOMCategory],
       [prod_productfamilyid])
WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
       DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
       FILLFACTOR = 90)
GO

EXEC sp_updatestats
GO

After adding these indexes, the screen rendering time should be reduced to approximately 6-8 seconds. This is not caused by a delay in retrieving data from the database; rather this remaining delay is caused by CRM attempting to process a huge number of products.

Status:

A case has been raised to the development team to investigate performance improvements in this area.