March 11, 2009 at 12:04 pm
Hi,
I am using a vendor purchased product for managing customers and i am getting very slow perfomance while doing a search, i profiled the database and found following query (QUERY 1) generated by the product for the search i am doing. I looked at the execution plan (Query1_Plan) of the query and i found table scan although i have indexes on all the required columns. On further investigating i found that "or (Cmli_Comm_LeadId=1854980)" part of the query is causing this table scan.
QUERY1:
select * , ROW_NUMBER() over(ORDER BY comm_datetime, Comm_Communicationid) AS rowranking
from sagecrm.vListCommunication
WHERE ((Comm_LeadId=1854980) or (Cmli_Comm_LeadId=1854980))
AND ( (COALESCE(Comm_Private, N'') = N'')
OR
((COALESCE(Comm_Private, N'') <> N'') AND (CmLi_Comm_UserId = 495))
)
I know the database struction and i know that "or (Cmli_Comm_LeadId=1854980)" part of where clause is not required it's just an extra cluase, i can get the same result by using "((Comm_LeadId=1854980)" only, so i modified the query and excluded "or (Cmli_Comm_LeadId=1854980)", and as expected query (Query 2) ran under one second and i looked at the query plan (Query2_Plan) and it is utilizing the indexes.
QUERY 2:
select * , ROW_NUMBER() over(ORDER BY comm_datetime, Comm_Communicationid) AS rowranking
from sagecrm.vListCommunication
WHERE ((Comm_LeadId=1854980))
AND ( (COALESCE(Comm_Private, N'') = N'')
OR
((COALESCE(Comm_Private, N'') <> N'') AND (CmLi_Comm_UserId = 495))
)
Now here is the problem, this query (Query 1) has been embeded in the dll of the product so i cannot modify this query in the application. So my question is is there a way to handle this situation at database level?
Please find attached the query plans for reference.
Thanks,
Usman
March 12, 2009 at 9:10 am
Without seeing the table and index DDL for the communication table it is hard to say exactly what the problem is. Comm_LeadId is in a nonclustered index, you may benefit from adding an index for Cmli_Comm_LeadId.
You should check with the vendor first to ensure that you don't invalidate your support. Ideally you should get them to fix the problem.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply