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.
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.
This issue has been fixed in version 7.1m of Sage CRM