Fermé

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.

Compétences : SQL

Voir plus : 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

Concernant l'employeur :
( 17 commentaires ) São Paulo, Brazil

N° du projet : #13086260

17 freelance ont fait une offre moyenne de 36 $ pour ce travail

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 en 1 jour
(105 Commentaires)
6.2
hwanghendra

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

30 $ USD en 1 jour
(107 Commentaires)
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 Plus

50 $ USD en 1 jour
(120 Commentaires)
6.2
vlad70

Hello Sir, for proper help - need have a data structure + sample date set similar with real (access to test server? or dump from dev server) + any valuable parameters - what mean slow? what will mean fast no win Plus

110 $ USD en 1 jour
(19 Commentaires)
5.1
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 Plus

30 $ USD en 1 jour
(24 Commentaires)
5.3
jagdishbhatt38

A proposal has not yet been provided

50 $ USD en 1 jour
(12 Commentaires)
4.2
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 en 2 jours
(18 Commentaires)
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 Plus

30 $ USD en 1 jour
(4 Commentaires)
2.1
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 Plus

25 $ USD en 1 jour
(1 Commentaire)
2.0
25 $ USD en 1 jour
(1 Commentaire)
1.9
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 Plus

25 $ USD en 3 jours
(0 Commentaires)
0.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 en 1 jour
(0 Commentaires)
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 Plus

25 $ USD en 1 jour
(0 Commentaires)
0.0
25 $ USD en 1 jour
(0 Commentaires)
0.0
mike613

hi there! my name is Mike. I am sure I can help you optimize your query, I am well versed in t-sql and as long as I have access to the back end Db I can examine the execution plan and schema to see what can be done. f Plus

35 $ USD en 1 jour
(0 Commentaires)
0.0
Papuas64

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

25 $ USD en 3 jours
(0 Commentaires)
0.0
23 $ USD en 6 jours
(0 Commentaires)
0.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 en 1 jour
(0 Commentaires)
0.0
20 $ USD en 1 jour
(0 Commentaires)
0.0