Suljettu

Slow TSQL query with datediff function

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

where

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.

Taidot: SQL

Näytä lisää: query tsql, datediff function using hours, optimize mysql slow query, mysql slow query, optimize slow query mysql, php mysql calculate datediff function, access update query slow odbc table, php function datediff, write javascript function mysql query, datediff function php, website can help letterhead, can quickbooks project number, scope drawings process control project, can create touch screen application cnet, can help cartoon, can start project php5, smpp server can help, slow select top query mssql, can marketing project work topic internet, php datediff function, english lines want translate hindi pls can help, function datediff php, mssql logging slow query, datediff php function, can find project call centre

About the Employer:
( 17 reviews ) São Paulo, Brazil

Projektin tunnus: #13086260

16 freelanceria on tarjonnut keskimäärin 32 $ tähän työhön

sumon355

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

26 $ USD 1 päivässä
(112 arvostelua)
6.5
hwanghendra

I may be able to help you .............................................................................

30 $ USD 1 päivässä
(108 arvostelua)
6.2
truongnguyen86

Hello there, i'm expert on TSQL, let me help you with this. Pls share me your bak file, actually we can create more indexes for faster query, and we can create function based index for faster, we will try and see. Wait Lisää

50 $ USD 1 päivässä
(127 arvostelua)
6.3
mahmoudgamal008

I have more than 5 years of professional and practical experience in TSQL, SQL Server and database development. I have resolved and optimized hundreds and hundreds of TSQL queries. I have more than 1800 answers in S Lisää

30 $ USD 1 päivässä
(30 arvostelua)
5.4
jagdishbhatt38

A proposal has not yet been provided

50 $ USD 1 päivässä
(24 arvostelua)
4.7
yubor

Ready to tune. Will need more info about schema and data. I am certified mssql server expert (mcse) - look at my profile.

80 $ USD 2 päivässä
(18 arvostelua)
3.7
DemonDeveloper

We are team of excellent developers. We have 2 year experience related to your project field. We can do your project and solve your all problems according to your requirements. We have read your description for project Lisää

30 $ USD 1 päivässä
(5 arvostelua)
2.3
25 $ USD 1 päivässä
(1 arvostelu)
1.9
nicsmith71

Hi, I can help with this but first I'd need to see a few things. Firstly I'd need the design of the 2 tables in question (tblContacts & tblCompanies). I'd also need to see a screenshot of the execution plan of the quer Lisää

25 $ USD 1 päivässä
(1 arvostelu)
2.0
bibhakarsaha

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.

15 $ USD 1 päivässä
(0 arvostelua)
0.0
Papuas64

Your problem in the wrong index. What SQL Server version?

25 $ USD 3 päivässä
(0 arvostelua)
3.0
ansariarifhusen

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

27 $ USD 1 päivässä
(0 arvostelua)
0.0
25 $ USD 1 päivässä
(0 arvostelua)
0.0
sunilmudunuri

I have 8 years of work experience working in a reputed MNC as a Database Lead, main skill set is Sql Server 2005/2008/2012, MYSQL Oracle (PL/SQL), MSBI, Data analytics using R-Programming and SAAS. I have experienc Lisää

25 $ USD 1 päivässä
(0 arvostelua)
0.0
ilatech

Hi, I'm a french software engineer, and I new to this website. I just create my compagny, and I develop software for compagnies. I worked for 10 years as a software developer, and for the last 3 years I worked with Lisää

25 $ USD 3 päivässä
(1 arvostelu)
0.0
20 $ USD 1 päivässä
(0 arvostelua)
0.0