0
votes

SQL Server: optimisation des requêtes

J'ai la requête suivante qui prend environ 4 minutes à exécuter.

SELECT c.id AS clid,
       h.id AS hlid,
       h.holdinNo,
       c.cliendID,
       c.clientName,
       h.floor,
       h.connect_radius
FROM   [db_land].[dbo].tbl_client AS c
       INNER JOIN [db_land].[dbo].tx_holding AS h
               ON c.id = h.clid
WHERE  h.status = 1
       AND h.connect_radius IS NOT NULL
       AND c.status = 1
       AND h.type = 'Residential'

J'ai trouvé que la jointure interne ne prend que quelques secondes.

DECLARE @tdate DATETIME = '2019-09-01 00:00:00.000'

SELECT c.id AS clid,
       h.id AS hlid,
       h.holdinNo,
       c.cliendID,
       c.clientName,
       h.floor,
       h.connect_radius
FROM   [db_land].[dbo].tbl_client AS c
       INNER JOIN [db_land].[dbo].tx_holding AS h
               ON c.id = h.clid
WHERE  h.status = 1
       AND h.connect_radius IS NOT NULL
       AND c.status = 1
       AND h.type = 'Residential'
       AND h.holdinNo NOT IN (SELECT holdingNo
                              FROM   [db_land].[dbo].tbl_bill
                              WHERE  year(date_month) = YEAR(@tdate)
                                     AND MONTH(date_month) = MONTH(@tdate)
                                     AND ( update_by IS NOT NULL
                                            OR ispay = 1 ))

C'est la vérification NOT IN qui prend beaucoup de temps. Comment puis-je optimiser cette requête? Pour moi, il est nécessaire d'exécuter la requête au moins en une minute.

sql sql-server

1 commentaires

Vous avez trois hypothèses différentes quant à la nature du problème. Téléchargez le XML pour le plan d'exécution réel afin que nous puissions voir précisément où se situe le problème

7 Réponses :

1
votes

Essayez de remplacer NOT IN par un LEFT JOIN de la table [db_land]. [dbo] .tbl_bill sur toutes les conditions et ajout de la clause WHERE holdingNo est nul afin que les lignes renvoyées soient les lignes non correspondantes:

select c.id as clid, h.id as hlid,h.holdinNo, c.cliendID, c.clientName, h.floor, h.connect_radius 
from [db_land].[dbo].tbl_client as c 
inner join [db_land].[dbo].tx_holding as h 
on c.id= h.clid
left join [db_land].[dbo].tbl_bill as b 
on b.holdingNo = h.holdinNo and year(b.date_month) = YEAR(@tdate) and MONTH(b.date_month) = MONTH(@tdate) 
and (b.update_by is not null or b.ispay = 1)
where h.status = 1 and h.connect_radius is not null and c.status=1 and h.type='Residential' and b.holdingNo is null

0 commentaires

0
votes

mettre la liste de filtres à variable, puis les appliquer le filtre

DECLARE @tdate DATETIME = '2019-09-01 00:00:00.000'

SELECT c.id AS clid,
       h.id AS hlid,
       h.holdinNo,
       c.cliendID,
       c.clientName,
       h.floor,
       h.connect_radius
FROM [db_land].[dbo].tbl_client AS c
INNER JOIN [db_land].[dbo].tx_holding AS h ON c.id= h.clid
WHERE h.status=1
  AND h.connect_radius IS NOT NULL
  AND c.status=1
  AND h.type='Residential'
  AND h.holdinNo NOT IN (filter)

eux appliquer le filtre

     DECLARE @filter TABLE  INSERT INTO @filter SELECT  FROM  [db_land].[dbo].tbl_bill

0 commentaires

2
votes

Assurez-vous que les prédicats des clauses WHERE et JOIN sont sargables. L'application d'une fonction à une colonne (par exemple YEAR (date_month) ) empêche l'utilisation efficace des index de la colonne.

Essayez plutôt cette expression pour éviter les fonctions. Il existe d'autres méthodes selon la version de SQL Server.

WHERE
    date_month >= DATEADD(day, 1, DATEADD(month, -1, EOMONTH(@tdate)))
    AND date_month < DATEADD(day, 1, DATEADD(month, 1, EOMONTH(@tdate)))

4 commentaires

DATEADD () et EOMONTH () ne sont pas des fonctions? !!! Votre code n'est pas conforme à vos affirmations.

Ce sont des fonctions mais elles ne sont pas appliquées à une colonne. Ils sont appliqués sur des variables

@MartinSmith, sauf si je manque quelque chose, date_month semble être un nom de colonne. Les fonctions sont également appliquées aux variables de la requête.

ma réponse a été à @forpas. Répondre à DATEADD () et EOMONTH () ne sont pas des fonctions? !!!

1
votes

Je recommanderais de changer le NOT IN en NOT EXISTS et d'ajouter un index:

WHERE . . . AND
      NOT EXISTS (SELECT 1
                  FROM [db_land].[dbo].tbl_bill b
                  WHERE b.holdingNo = h.holdingNo AND
                        b.date_month >= DATEFROMPARTS(YEAR(@tdate), MONTH(@tdate), 1) AND
                        b.date_month < DATEADD(month, 1, DATEFROMPARTS(YEAR(@tdate), MONTH(@tdate), 1)) AND
                        (b.update_by IS NOT NULL OR b.ispay = 1
                 )

Ensuite, l'index que vous voulez est le tbl_bill (holdingNo, date_month, update_by, ispay) .

0 commentaires

1
votes

Placez votre sous-requête dans la table temporaire:

DECLARE @tdate DATETIME = '2019-09-01 00:00:00.000'

SELECT holdingNo 
into #TmpholdingNo
FROM   [db_land].[dbo].tbl_bill
WHERE  year(date_month) = YEAR(@tdate)
        AND MONTH(date_month) = MONTH(@tdate)
        AND ( update_by IS NOT NULL
        OR ispay = 1 )

SELECT c.id AS clid,
       h.id AS hlid,
       h.holdinNo,
       c.cliendID,
       c.clientName,
       h.floor,
       h.connect_radius
FROM   [db_land].[dbo].tbl_client AS c
       INNER JOIN [db_land].[dbo].tx_holding AS h
               ON c.id = h.clid
WHERE  h.status = 1
       AND h.connect_radius IS NOT NULL
       AND c.status = 1
       AND h.type = 'Residential'
       AND h.holdinNo NOT IN (SELECT holdingNo from #TmpholdingNo) 

drop table #TmpholdingNo

0 commentaires

0
votes

Plutôt que d'utiliser des fonctions dans votre clause WHERE , essayez de calculer les dates de début et de fin du filtre, l'utilisation de OPTION (RECOMPILE) peut aider SQL à utiliser les valeurs réelles de vos variables dans votre plan de requête. Je changerais également NOT IN en NOT EXISTS :

DECLARE @tdate DATETIME = '2019-09-01 00:00:00.000'
DECLARE @startDate DATE = DATEFROMPARTS(YEAR(@tdate), MONTH(@tdate), 1)
DECLARE @endDate DATE = DATEADD(day,1,EOMONTH(@tdate))


SELECT c.id AS clid,
       h.id AS hlid,
       h.holdinNo,
       c.cliendID,
       c.clientName,
       h.floor,
       h.connect_radius
FROM   [db_land].[dbo].tbl_client AS c
       INNER JOIN [db_land].[dbo].tx_holding AS h
               ON c.id = h.clid
WHERE  h.status = 1
       AND h.connect_radius IS NOT NULL
       AND c.status = 1
       AND h.type = 'Residential'
       AND NOT EXISTS (SELECT holdingNo
                       FROM   [db_land].[dbo].tbl_bill
                       WHERE  holdingNo = h.holdinNo AND
                       date_month >= @startDate AND
                       date_month < @endDate AND 
                                     AND ( update_by IS NOT NULL
                                            OR ispay = 1 )) 
OPTION (RECOMPILE)

0 commentaires

0
votes

essayer, essayez ceci:

select main.* from 

    (SELECT c.id AS clid,
           h.id AS hlid,
           h.holdinNo,
           c.cliendID,
           c.clientName,
           h.floor,
           h.connect_radius
    FROM   [db_land].[dbo].tbl_client AS c
           INNER JOIN [db_land].[dbo].tx_holding AS h
                   ON c.id = h.clid
    WHERE  h.status = 1
           AND h.connect_radius IS NOT NULL
           AND c.status = 1
           AND h.type = 'Residential')main

left join 

    (select holdingNo from 
        (SELECT holdingNo, update_by, ispay
        FROM   [db_land].[dbo].tbl_bill
        WHERE  year(date_month) = YEAR(@tdate)
        AND MONTH(date_month) = MONTH(@tdate))bill1
    where update_by IS NOT NULL OR ispay = 1)bill2

on main.holdinNo = bill2.holdinNo


where bill2.holdinNo is null

0 commentaires