Slow Advanced Find RRS feed

  • Question

  • I have a client that is trying to run an Advanced Find in CRM 4.0 that is taking much longer than I would expect, often timing out.

    Advanced Find as follows:

    Look For: Customers
    Contact Preferences (custom entity)
        Contact Preference = VIP EMAIL - TRUE
    Orders (Customer)
        Order Products (Order)
            Size = 36DD

    From a SQL trace I located the query that is being run:

    select  DISTINCT  top 251 contact0.FullName as 'fullname', contact0.Telephone1 as 'telephone1', contact0.EMailAddress1 as 'emailaddress1', contact0.new_CustomerID as 'new_customerid', contact0.ContactId as 'contactid'

    from Contact as contact0 join new_contactpreference_contact as new_contactpreference_contact0 on (contact0.ContactId  =  new_contactpreference_contact0.contactid)

    join new_contactpreference as aa on (new_contactpreference_contact0.new_contactpreferenceid  =  aa.new_contactpreferenceId and (aa.new_contactpreferenceId = @new_contactpreferenceId0))

    join SalesOrder as ab on (contact0.ContactId  =  ab.CustomerId) join SalesOrderDetail as ac on (ab.SalesOrderId  =  ac.SalesOrderId and (ac.new_Size = @new_Size0))

    where ((contact0.DeletionStateCode in (@DeletionStateCode0))) and ((aa.DeletionStateCode in (@DeletionStateCode1))) and ((ab.DeletionStateCode in (@DeletionStateCode2))) and ((ac.DeletionStateCode in (@DeletionStateCode3))) 

    order by contact0.FullName asc

    This query takes just 6 seconds to run! The above query also appears in the Process Monitor in SQL the whole time the Advanced Find is running.

    Can anyone explain what CRM is doing beyond this query that makes it so slow? MY initial response to a slow running Advanced Find was to add indexes, but I can't see how this will help when the SQL query takes so little time.

    • Edited by Bozwell99 Tuesday, June 10, 2014 12:14 PM typo
    Tuesday, June 10, 2014 10:30 AM