Discussion :

[ludo00002]

Je voudrais faire une requête qui me retourne les stocks qui n'ont pas eu de mouvements entre 2 dates.
Voici mon 1er essai:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
select stock_ID
from Stock
where stock_ID not in (
  select Stock_ID
  from Mouvement
  where DateMvt>DateMin and DateMvt<=DateMax
  )

La table mouvement contient environ 25000 enregistrements.
La table stock contients environs 4000 enregistrements.
Le champ stock_ID de la table Mouvement est indexé.
Le temps d'exécution de la requête est d'1 minute environ. C'est assez énorme pour moi.
J'ai donc fait un essais avec une procédure stockée:
Contenu de la procédure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
declare Ok  integer;
begin
 for select Stock_ID
     from Stock
     into :Stock_ID
 do
   begin
    select Count(*)
    from Mouvement_Stock
    where Stock_ID=:Stock_ID
    into :Ok;
    if (Ok>=1) then
     suspend;
   end
end

Le temps d'exécution est de quelques secondes.
Je ne comprends pas cette différence de temps d'exécution.
Quelqu'un pourrait me l'expliquer?

[evarisnea]

je ne suis pas expert SQL mais lorsque je regarde la première requête il apparaît que pour chaque enregistrement de la table stock, tous les enregistrement de la table mouvement seront analysés afin de déterminer si le stock_id y est compris ou pas. ce qui donne un parcours de 4000x25000 lignes par le moteur SGBD.
comme le confirme d'ailleurs le plan d'exécution de la requête sous IBExpert

Plan
PLAN (T_MOUVEMENT NATURAL)
PLAN (T_STOCK NATURAL)

aucun index n'est utilisé.

ce que tu cherches à faire est en fait une différence (mot clé sql EXCEPT). celle ci n'est pas supportée par firebird 2.1 (à ma connaissance).
mais comme mentionné dans l'exemple 18 ici dans l'article de SQLPro LE SQL de A à Z : 4e partie - groupage et ensembles, tu peux obtenir le même résultat ainsi

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT s.stock_id
  FROM T_STOCK s
    LEFT OUTER JOIN T_MOUVEMENT m
      ON (s.STOCK_ID = m.stock_id)AND(m.DATEMVT BETWEEN :date1 AND :date2)
  GROUP BY s.STOCK_ID
  HAVING COUNT(m.stock_id) = 0

voilà son plan d'exécution :

Plan
PLAN JOIN (S ORDER PK_T_STOCK, M INDEX (FK_T_MOUVEMENT_1))

tu peux constater qu'ici l'index est utilisé

j'ai repris ton schéma,

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE T_STOCK (
    STOCK_ID   INTEGER NOT NULL,
    STOCK_NOM  VARCHAR(32)
);
ALTER TABLE T_STOCK ADD CONSTRAINT PK_T_STOCK PRIMARY KEY (STOCK_ID);
 
CREATE TABLE T_MOUVEMENT (
    STOCK_ID  INTEGER,
    DATEMVT   DATE,
    QTE       INTEGER
);
ALTER TABLE T_MOUVEMENT ADD CONSTRAINT FK_T_MOUVEMENT_1 FOREIGN KEY (STOCK_ID) REFERENCES T_STOCK (STOCK_ID) ON DELETE CASCADE ON UPDATE CASCADE;

avec 4000 enregistrement pour la table stock et 25000 pour mouvement.
et selon les tests la première requête (celle que tu as utilisée) fait effectivement plus d'une minute et demi pour s'exécuter, tandis que celle que je te propose ne fait même pas 1 seconde

[ludo00002]

Merci evarisnea. Effectivement ça fonctionne bien!