CODEWITHSUNDEEP
sqlsql-serverquery-optimization
fatiDev
fatiDev

Reputation: 5992

Optimize sql Query which take too long to execute

The following sql query is taking to much to return a result 

SELECT count(*)
FROM List_declarations
WHERE  Id_antenne IN (
   SELECT id
   FROM Service
   WHERE id_direction IN (
      SELECT id_dr
      FROM affectation_service_dr
      WHERE id_service = 15
   )
)

How I can do better ?

Upvotes: 0

Views: 143

Answers (4)

fatiDev
fatiDev

Reputation: 5992

I tried everyone proposition but nothing seems to be the solution , the time consumed by the request is still 37s

contemplating the request , I realized that the query within the in clause is executed for every row in table list_declarations which contains a huge amount of data . and the following sql code worked nickel (1s) :

declare  @table table( idantenne nvarchar(20))
insert into @table select ID from Service where id_direction in (select id_dr from affectation_service_dr where id_service = 15)
Declare @string AS Nvarchar(MAX) -- It must not be MAX if you have few numbers 
SELECT  @string = COALESCE(@string + ',', '') + idantenne
FROM   @table where idantenne IS NOT NULL
declare @req nvarchar(max)
set @req = 'SELECT * FROM List_declarations Where 1=1  and Id_antenne in (' + @string +' )'
exec (@req)

Upvotes: 0

KumarHarsh
KumarHarsh

Reputation: 5094

if Read Uncommitted data are not a concern here.If it concern then still you can try removing with (nolock)

SELECT count(*)
FROM List_declarations LD WITH (NOLOCK)
WHERE  exists (
   SELECT id
   FROM [Service] S WITH (NOLOCK)
   inner join affectation_service_dr dr WITH (NOLOCK)
   on s.id_direction=dr.id_dr
    WHERE ld.Id_antenne=s.id and dr.id_service = 15
   )

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74605

Try this:

SELECT count(*)
FROM 
  List_declarations d
  INNER JOIN Service 
  ON d.Id_antenne = s.id

  INNER JOIN affectation_service_dr a
  s.id_direction = a.id_dr

WHERE 
  a.id_service = 15
  • it might make a difference, it might not (but it's generally a better way to write a query of this nature)

Of inestimable importance, is to ensure the following are indexed:

affectation_service_dr.id_service 
Service.id_direction 
List_declarations.Id_antenne

If there are still performance issues that cannot be easily explained by a huge number of rows (e.g. it's taking 38s but it IS returning a count of 20348957389956845) then I recommend you modify your question to include the plan explanation ( https://dev.mysql.com/doc/workbench/en/wb-performance-explain.html )

Upvotes: 1

Rigerta
Rigerta

Reputation: 4039

I would think of changing the IN's to JOIN's:

select count(*) 
from list_declarations d join service s on d.id_antenne = s.id
                         join affectation_service_dr sdr on sdr.id_dr = s.id_direction
where sdr.id_service = 15

Then I would again check performance and see if there are any indexes on the columns used in the where clause and on the foreign keys.

Upvotes: 1

Related Questions