Chuis en train de patater le serveur de prod SQL Server

Chuis en train de patater le serveur de prod SQL Server - SQL/NoSQL - Programmation

Marsh Posté le 02-08-2005 à 10:16:02    

Entre les sous-select à gogo sur une table de 800 000 lignes et l'émulation du "FULL JOIN", il n'a pas l'air d'apprécier des masses :D
 

Code :
  1. select isnull(a.assetid, ah.assetid) assetid
  2. from asset a, assethist ah
  3. where ah.modifieddate = (
  4. select max(ah2.modifieddate)
  5. from assethist ah2
  6. where datediff(d, ah2.modifieddate, getdate()) >= 14
  7. and (datediff(d, ah2.deleteddate, getdate()) > 14 or ah2.deleteddate is null)
  8. and ah2.assetid = ah.assetid
  9. )
  10. and (select max(ah2.active)
  11. from assethist ah2
  12. where datediff(d, ah2.modifieddate, getdate()) >= 14
  13. and (datediff(d, ah2.deleteddate, getdate()) > 14 or ah2.deleteddate is null)
  14. and ah2.assetid = ah.assetid) <> 'I'
  15. --and a.assetid = 90257
  16. and datediff(d, ah.modifieddate, getdate()) >= 14
  17. and (datediff(d, ah.deleteddate, getdate()) > 14 or ah.deleteddate is null)
  18. and (select max(ah2.deleteddate)
  19. from assethist ah2
  20. where datediff(d, ah2.modifieddate, getdate()) >= 14
  21. and (datediff(d, ah2.deleteddate, getdate()) > 14 or ah2.deleteddate is null)
  22. and ah2.assetid = ah.assetid) is null
  23. and (a.assetid *= ah.assetid or not exists (select ah2.assetid from assethist ah2 where ah2.assetid = a.assetid))
  24. and a.active = 'A'
  25. and datediff(d, a.modifieddate, getdate()) >= 14
  26. group by a.assetid, ah.assetid

Reply

Marsh Posté le 02-08-2005 à 10:16:02   

Reply

Marsh Posté le 02-08-2005 à 10:17:34    

Ca fait quand même 20 minutes qu'il pédale dans la semoule... Et pourtant je tape que dans deux table.
 
Ca inspire le respect non ? :sol:


Message édité par Arjuna le 02-08-2005 à 10:17:54
Reply

Marsh Posté le 02-08-2005 à 10:47:31    

non, tu tapes une fois dans asset et 5 fois dans assethist :p
 
et puis je vois pas trop pourquoi tu tapes autant de fois dans assethist, tu pourrais grouper quelques unes des sous-requêtes, non ?
 
Par exemple :
 

Code :
  1. select isnull(a.assetid, ah.assetid) assetid
  2. from asset a,
  3.      assethist ah,
  4.      (select assetid,
  5.              max(ah2.modifieddate) as max_modifieddate,
  6.              max(ah2.deleteddate) as max_deleteddate,
  7.              max(ah2.active) as max_active
  8.       from assethist
  9.       where datediff(d, ah2.modifieddate, getdate()) >= 14
  10.       and (datediff(d, ah2.deleteddate, getdate()) > 14 or ah2.deleteddate is null)
  11.       group by assetid
  12.       having max(ah2.active) <> 'I' and max(ah2.deleteddate) is null) sub
  13. where sub.assetid = ah.assetid
  14. and   ah.modifieddate = sub.max_modifieddate
  15. and   datediff(d, ah.modifieddate, getdate()) >= 14
  16. and   (datediff(d, ah.deleteddate, getdate()) > 14 or ah.deleteddate is null)
  17. and   (a.assetid *= ah.assetid or not exists (select ah2.assetid from assethist ah2 where ah2.assetid = a.assetid))
  18. and   a.active = 'A'
  19. and   datediff(d, a.modifieddate, getdate()) >= 14
  20. group by a.assetid, ah.assetid;

Reply

Marsh Posté le 02-08-2005 à 11:02:37    

j'avais pas envie :D
 
Ca fait une heure que ça tourne :D
 
Je vais tester ta requête pour voir :)

Reply

Marsh Posté le 02-08-2005 à 11:46:34    

Et d'ailleurs je comprends pas trop la jointure sur assethist (ah), le *= est une jointure externe ?
 
Que vient faire le not exists ???
 
Et pourquoi sélectionner isnull(a.assetid, ah.assetid) quand on fait un group by a.assetid, ah.assetid ?
 
Un peu étrange ta requête quand même :D

Reply

Marsh Posté le 02-08-2005 à 12:27:10    

le exists vient faire une jointure *=* c'est à dire une "full join" que SQL Server ne supporte pas.
 
En fait, je peux avoir des données dans "assethist" qui ne sont pas dans "asset", tout comme je peux avoir des données dans "asset" qui ne sont pas dans "assethist".
 
Et moi, je veux l'état de la table Asset d'il y a 14 jours :pt1cable:
 
Il y a un trigger sur Asset qui recopie les valeurs initiales dans AssetHist à chaque fois qu'on modifie une ligne d'Asset.
Ensuite, lorsqu'on supprime un Asset, il est physiquement supprimé de cette table, mais un champ "deletedDate" est rempli dans la table AssetHist pour la ligne où on a recopié la ligne supprimée.
 
Ensuite, pour une raison inconnue (certainement des modifications de la table via Access) il peut y avoir pour la même milli-seconde près une dizaine de lignes... avec un flag "active" différent dans la table AssetHist.
Evidement, dès qu'on trouve un I (inactif, il faut écarter l'asset).
 
Enfin... C'est un peu le bordel.
 
Ta requête est aussi lente que la mienne et elle est fausse (à cause de cette histoire de flags différents à deux heures identiques)
 
Là, le dev qui s'occupe de ça s'amuse à rechercher tous les assets qui datent de plus de 14 jours dans une table temporaire, et les vire à coup de delete en fonction des cas spécifiques. C'est un peu le bordel, mais c'est bien plus rapide, j'ai l'impression que c'est la seule solution sans pourrir le serveur (il est en dessous des 2 minutes)

Reply

Marsh Posté le 02-08-2005 à 12:28:09    

sinon, le group by sous SQL Server peut ne porter que sur les champs qui participent aux lignes retournées, pas besoin de recopier les expression comme dans Oracle, MySQL ou PostGre (petite simplification maison chez Microsoft)

Reply

Marsh Posté le 02-08-2005 à 12:29:30    

Ah nan, il a abandonné la table temporaire, il a juste un truc qui fait 20 pages de long. M'enfin ça tourne en 10 secondes, c'est pas mal :D

Reply

Marsh Posté le 02-08-2005 à 13:51:41    

Je squatte ton post pour demander si quelqu'un sait comment faire un *= en MySQL
 
Merci
 
P.S : Avec ma table avec des milions de lignes je patate mon serveur beaucoup plus violemment

Reply

Marsh Posté le 02-08-2005 à 13:57:51    

C'est toujours pour le bench Arjuna?
(Dsl ca fait une semaine que je suis déco)

Reply

Marsh Posté le 02-08-2005 à 13:57:51   

Reply

Marsh Posté le 02-08-2005 à 14:57:29    

Nope, c'est juste une requête que dois faire un collègue pour une appli :)
 

Code :
  1. SELECT af.AssetID,
  2. ISNULL(a4.ItemCategory, MIN(af.ItemCategory)) AS ItemCategory
  3. FROM (
  4.  (
  5.   SELECT ah.AssetID,
  6.    ah.CreateDate,
  7.    ah.ModifiedDate,
  8.    ah.DeletedDate,
  9.    ah.ItemCategory,
  10.    ah.ItemClass,
  11.    ah.Active,
  12.    ah.LineType
  13.   FROM AssetHist AS ah
  14.   UNION ALL
  15.   SELECT a.AssetID,
  16.    a.CreateDate,
  17.    a.ModifiedDate,
  18.    NULL AS DeleteDate,
  19.    a.ItemCategory,
  20.    a.ItemClass,
  21.    a.Active,
  22.    a.LineType
  23.   FROM Asset AS a
  24.  ) AS af
  25.  INNER JOIN
  26.  (
  27.   SELECT af1.AssetID,
  28.    MAX(af1.ModifiedDate) AS ModifiedDate
  29.   FROM (
  30.     SELECT ah1.AssetID,
  31.      ah1.CreateDate,
  32.      ah1.ModifiedDate,
  33.      ah1.DeletedDate,
  34.      ah1.ItemCategory,
  35.      ah1.ItemClass,
  36.      ah1.Active,
  37.      ah1.LineType
  38.     FROM AssetHist AS ah1
  39.     UNION ALL
  40.     SELECT a1.AssetID,
  41.      a1.CreateDate,
  42.      a1.ModifiedDate,
  43.      NULL AS DeleteDate,
  44.      a1.ItemCategory,
  45.      a1.ItemClass,
  46.      a1.Active,
  47.      a1.LineType
  48.     FROM Asset AS a1
  49.    ) AS af1
  50.   WHERE (
  51.     af1.ItemCategory = 'Computer - Desktop'
  52.     OR
  53.     af1.ItemCategory = 'Computer - Laptop'
  54.    )
  55.    AND
  56.    af1.LineType = 'B'
  57.    AND
  58.    DATEDIFF(d, af1.ModifiedDate, GETDATE()) >= 14
  59.   GROUP BY
  60.    af1.AssetID
  61.  ) AS af2
  62.  ON af.AssetID = af2.AssetID AND af.ModifiedDate = af2.ModifiedDate
  63. )
  64. LEFT JOIN
  65. Asset AS a4
  66. ON
  67. af.AssetID = a4.AssetID
  68. WHERE NOT EXISTS
  69. (
  70.  SELECT NULL
  71.  FROM (
  72.    SELECT ah2.AssetID,
  73.     ah2.CreateDate,
  74.     ah2.ModifiedDate,
  75.     ah2.DeletedDate,
  76.     ah2.ItemCategory,
  77.     ah2.ItemClass,
  78.     ah2.Active,
  79.     ah2.LineType
  80.    FROM AssetHist AS ah2
  81.    UNION ALL
  82.    SELECT a2.AssetID,
  83.     a2.CreateDate,
  84.     a2.ModifiedDate,
  85.     NULL AS DeleteDate,
  86.     a2.ItemCategory,
  87.     a2.ItemClass,
  88.     a2.Active,
  89.     a2.LineType
  90.    FROM Asset AS a2
  91.   ) AS af3
  92.   INNER JOIN
  93.   (
  94.    SELECT af4.AssetID,
  95.     MAX(af4.ModifiedDate) AS ModifiedDate
  96.    FROM (
  97.      SELECT ah3.AssetID,
  98.       ah3.CreateDate,
  99.       ah3.ModifiedDate,
  100.       ah3.DeletedDate,
  101.       ah3.ItemCategory,
  102.       ah3.ItemClass,
  103.       ah3.Active,
  104.       ah3.LineType
  105.      FROM AssetHist AS ah3
  106.      UNION ALL
  107.      SELECT a3.AssetID,
  108.       a3.CreateDate,
  109.       a3.ModifiedDate,
  110.       NULL AS DeleteDate,
  111.       a3.ItemCategory,
  112.       a3.ItemClass,
  113.       a3.Active,
  114.       a3.LineType
  115.      FROM Asset AS a3
  116.     ) AS af4
  117.    WHERE af4.LineType = 'B'
  118.     AND
  119.     DATEDIFF(d, af4.ModifiedDate, GETDATE()) >= 14
  120.    GROUP BY
  121.     af4.AssetID
  122.   ) AS af5
  123.   ON af3.AssetID = af5.AssetID AND af3.ModifiedDate = af5.ModifiedDate
  124.  WHERE (
  125.    af3.Active = 'I'
  126.    OR
  127.    DATEDIFF(d, af3.DeletedDate, GETDATE()) > 14
  128.   )
  129.   AND af3.AssetID = af.AssetID
  130. )
  131. OR
  132. EXISTS
  133. (
  134.  SELECT NULL
  135.  FROM Asset AS a5
  136.  WHERE a5.AssetID = af.AssetID
  137.   AND
  138.   a5.LineType = 'B'
  139.   AND
  140.   DATEDIFF(d, a5.ModifiedDate, GETDATE()) >= 14
  141.   AND
  142.   a5.Active = 'A'
  143. )
  144. GROUP BY
  145. af.AssetID,
  146. a4.ItemCategory
  147. ORDER BY
  148. af.AssetID


 
Sinon, un *= c'est un "left outer join".

Reply

Marsh Posté le 02-08-2005 à 15:49:06    

V'là le bouquet final :
 
Ca ressemble presque à de l'ASCII-art :love:
 

Code :
  1. SELECT ParcOld.AssetID,
  2. ParcOld.ItemCategory,
  3. aOld.Code
  4. FROM (
  5.  (
  6.   SELECT af.AssetID,
  7.    ISNULL(a4.ItemCategory, MIN(af.ItemCategory)) AS ItemCategory
  8.   FROM (
  9.     (
  10.      SELECT ah.AssetID,
  11.       ah.CreateDate,
  12.       ah.ModifiedDate,
  13.       ah.DeletedDate,
  14.       ah.ItemCategory,
  15.       ah.ItemClass,
  16.       ah.Active,
  17.       ah.LineType
  18.      FROM AssetHist AS ah
  19.      UNION ALL
  20.      SELECT a.AssetID,
  21.       a.CreateDate,
  22.       a.ModifiedDate,
  23.       NULL AS DeleteDate,
  24.       a.ItemCategory,
  25.       a.ItemClass,
  26.       a.Active,
  27.       a.LineType
  28.      FROM Asset AS a
  29.     ) AS af
  30.     INNER JOIN
  31.     (
  32.      SELECT af1.AssetID,
  33.       MAX(af1.ModifiedDate) AS ModifiedDate
  34.      FROM (
  35.        SELECT ah1.AssetID,
  36.         ah1.CreateDate,
  37.         ah1.ModifiedDate,
  38.         ah1.DeletedDate,
  39.         ah1.ItemCategory,
  40.         ah1.ItemClass,
  41.         ah1.Active,
  42.         ah1.LineType
  43.        FROM AssetHist AS ah1
  44.        UNION ALL
  45.        SELECT a1.AssetID,
  46.         a1.CreateDate,
  47.         a1.ModifiedDate,
  48.         NULL AS DeleteDate,
  49.         a1.ItemCategory,
  50.         a1.ItemClass,
  51.         a1.Active,
  52.         a1.LineType
  53.        FROM Asset AS a1
  54.       ) AS af1
  55.      WHERE (
  56.        af1.ItemCategory = 'Computer - Desktop'
  57.        OR
  58.        af1.ItemCategory = 'Computer - Laptop'
  59.       )
  60.       AND
  61.       af1.LineType = 'B'
  62.       AND
  63.       DATEDIFF(d, af1.ModifiedDate, GETDATE()) >= 14
  64.      GROUP BY
  65.       af1.AssetID
  66.     ) AS af2
  67.     ON af.AssetID = af2.AssetID AND af.ModifiedDate = af2.ModifiedDate
  68.    )
  69.    LEFT JOIN
  70.    Asset AS a4
  71.    ON
  72.    af.AssetID = a4.AssetID
  73.   WHERE NOT EXISTS
  74.    (
  75.     SELECT NULL
  76.     FROM (
  77.       SELECT ah2.AssetID,
  78.        ah2.CreateDate,
  79.        ah2.ModifiedDate,
  80.        ah2.DeletedDate,
  81.        ah2.ItemCategory,
  82.        ah2.ItemClass,
  83.        ah2.Active,
  84.        ah2.LineType
  85.       FROM AssetHist AS ah2
  86.       UNION ALL
  87.       SELECT a2.AssetID,
  88.        a2.CreateDate,
  89.        a2.ModifiedDate,
  90.        NULL AS DeleteDate,
  91.        a2.ItemCategory,
  92.        a2.ItemClass,
  93.        a2.Active,
  94.        a2.LineType
  95.       FROM Asset AS a2
  96.      ) AS af3
  97.      INNER JOIN
  98.      (
  99.       SELECT af4.AssetID,
  100.        MAX(af4.ModifiedDate) AS ModifiedDate
  101.       FROM (
  102.         SELECT ah3.AssetID,
  103.          ah3.CreateDate,
  104.          ah3.ModifiedDate,
  105.          ah3.DeletedDate,
  106.          ah3.ItemCategory,
  107.          ah3.ItemClass,
  108.          ah3.Active,
  109.          ah3.LineType
  110.         FROM AssetHist AS ah3
  111.         UNION ALL
  112.         SELECT a3.AssetID,
  113.          a3.CreateDate,
  114.          a3.ModifiedDate,
  115.          NULL AS DeleteDate,
  116.          a3.ItemCategory,
  117.          a3.ItemClass,
  118.          a3.Active,
  119.          a3.LineType
  120.         FROM Asset AS a3
  121.        ) AS af4
  122.       WHERE af4.LineType = 'B'
  123.        AND
  124.        DATEDIFF(d, af4.ModifiedDate, GETDATE()) >= 14
  125.       GROUP BY
  126.        af4.AssetID
  127.      ) AS af5
  128.      ON af3.AssetID = af5.AssetID AND af3.ModifiedDate = af5.ModifiedDate
  129.     WHERE (
  130.       af3.Active = 'I'
  131.       OR
  132.       DATEDIFF(d, af3.DeletedDate, GETDATE()) > 14
  133.      )
  134.      AND af3.AssetID = af.AssetID
  135.    )
  136.    OR
  137.    EXISTS
  138.    (
  139.     SELECT NULL
  140.     FROM Asset AS a5
  141.     WHERE a5.AssetID = af.AssetID
  142.      AND
  143.      a5.LineType = 'B'
  144.      AND
  145.      DATEDIFF(d, a5.ModifiedDate, GETDATE()) >= 14
  146.      AND
  147.      a5.Active = 'A'
  148.    )
  149.   GROUP BY
  150.    af.AssetID,
  151.    a4.ItemCategory
  152.  ) AS ParcOld
  153.  INNER JOIN
  154.  (
  155.   SELECT aacf.AssetID,
  156.    ISNULL(aac2.AccountID, MAX(aacf.AccountID)) AS AccountID
  157.   FROM (
  158.     (
  159.      SELECT aach.AssetID,
  160.       aach.AccountID,
  161.       aach.ModifiedDate,
  162.       aach.DeletedDate
  163.      FROM AssetAccountChargeHist AS aach
  164.      UNION ALL
  165.      SELECT aac.AssetID,
  166.       aac.AccountID,
  167.       aac.ModifiedDate,
  168.       NULL AS DeleteDate
  169.      FROM AssetAccountCharge AS aac
  170.     ) AS aacf
  171.     INNER JOIN
  172.     (
  173.      SELECT aacf1.AssetID,
  174.       MAX(aacf1.ModifiedDate) AS ModifiedDate
  175.      FROM (
  176.        SELECT aach1.AssetID,
  177.         aach1.ModifiedDate,
  178.         aach1.DeletedDate
  179.        FROM AssetAccountChargeHist AS aach1
  180.        UNION ALL
  181.        SELECT aac1.AssetID,
  182.         aac1.ModifiedDate,
  183.         NULL AS DeleteDate
  184.        FROM AssetAccountCharge AS aac1
  185.       ) AS aacf1
  186.       WHERE DATEDIFF(d, aacf1.ModifiedDate, GETDATE()) >= 14
  187.       GROUP BY
  188.        aacf1.AssetID
  189.     ) AS aacf2
  190.     ON aacf.AssetID = aacf2.AssetID AND aacf.ModifiedDate = aacf2.ModifiedDate
  191.    )
  192.    LEFT JOIN
  193.    AssetAccountCharge AS aac2
  194.    ON aacf.AssetID = aac2.AssetID AND aacf.ModifiedDate = aac2.ModifiedDate
  195.   GROUP BY
  196.    aacf.AssetID,
  197.    aac2.AccountID
  198.  ) AS aacOld
  199.  ON ParcOld.AssetID = aacOld.AssetID
  200. )
  201. INNER JOIN
  202. Account AS aOld
  203. ON aacOld.AccountID = aOld.AccountID
  204. ORDER BY
  205. ParcOld.AssetID

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed