When using a GCRM integration with OfficeLine, a performance problem may be encountered when selecting Products in a Quote.


When a new quote is created, on selecting the product and product family the dropdown lists may take a long time to load.


The issue is caused by a SQL statement which may take a long time to complete.

select distinct(pric_productId)
from Pricing
    join vQuotes on pric_pricinglistid = quot_pricinglistid
    join NewProduct on prod_productID = pric_productID
    left outer join ProductFamily on PrFa_ProductFamilyID = prod_productfamilyid
    join UOMFamily on prod_UOMCategory = UOMF_FamilyID
    join UOM on UOMF_FamilyID = UOM_FamilyID and pric_UOMID = UOM_UOMID
where  pric_active = N'Y' and  prod_active = N'Y' and  Quot_OrderQuoteId=11
    and Prod_ProductFamilyID=4 and  UOMF_Active = N'Y'
    and UOM_Active = N'Y' and (( prfa_active = N'Y') or (prfa_productfamilyid is null))



A new index can be created using the following script:

CREATE NONCLUSTERED INDEX PRICING_pricActive_IDX ON PRICING ([pric_Active], [pric_UOMID],pric_productId, [pric_PricingListID])


The Selection of product and product family should now be as quick as normal.

More information:

This issue has been fixed in version 7.1m of Sage CRM