I have a query which runs fast when the date clause "and datediff(day,con2.DT_DateIncluded),'2017-01-01')<=0" in the code below isn't used in the query, but runs slowly when it is included. Though it runs fast when I run just the part "select top 2 ID_Contact...", even including the date clause. I have this query on a classic ASP application, and it can't be converted in to a stored procedure (project scope reasons). Can you help me find a way to improve the performance of the full query just by changing the query code?
Obs: I have already tried: con2.DT_DateIncluded >= '2017-01-01'.
select distinct top 10 ID_Contact, NO_CodCompany from tblContacts con1 where ID_Contact in (
select top 2 ID_Contact
from tblContacts con2
inner join tblCompanies cp on con2.NO_CodCompany = cp.ID_Company
con2.NO_CodCompany = con1.NO_CodCompany
and datediff(day,con2.DT_DateIncluded),'2017-01-01')<=0 )
My goal with this query is to obtain the contacts from companies but limited to "n" contacts per company (on the query I used on the example above, n=2). The query I posted as example does the job perfectly, but it gets "impossibly slow" when I add the date clause in it. This date field is already indexed, and I have already changed the datediff expression to a simple DT_DateIncluded>='20170101'. But still no joy...
I would be happy with a different query that performs normally with the date clause or without it.
17 freelance ont fait une offre moyenne de 36 $ pour ce travail
Hello, As an expert in writing sql queries, i am very much interested to fix this issue. I understand the issue and i can fix it. Please let me know if you are interested. Thanks
Hi I have 5+ years of experience in sql and also performance tuning. if you are interested add me on Skype so we can start asap. As it's urgent for you. Skype: arifansari40 waiting.. Thanks Arif
I am an expert in fine-tuning queries and as I have worked on ORACLE databases, I feel I can assist you with the work and help you deliver it. I have an experience of working in leading MNCs with the same task.