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.
