Imbriquer tous les enregistrements nulls par rapport à la totalité des enregistrements [Résolu]

[witch]

Bonjour,


J'aimerai récupérer les fiches dont tous les résultats de la sous requête contiennent des valeurs null, pour toute une valeur Code, la table Client et Import ont le champs indice comme relation, des fiches peuvent contenir des valeurs nulls et d'autres pas par rapport à chaque valeur Code, et ce que je veux récupérer c'est uniquement ceux qui ont des valeurs nulls pour tous les enregistrement par rapport à la totalité des enregistrements de valeur Code=1 par exemple.

Code :

1
2
3
SELECT date_import ,Code,count(*) FROM IMPORT
WHERE indice >=2952868 AND   (  EXISTS( SELECT count(*) FROM client WHERE STATUS IS NULL )  ) 
GROUP BY date_import, Code ORDER BY date_import,Code

Pas très sure que je suis assez claire

Une remarque?

[aieeeuuuuu]

Envoyé par witch

Pas très sure que je suis assez claire

Heu... pas très clair en effet.

Dans un premier temps, votre predicat EXISTS sera toujours vrai, puisque COUNT(*) retourne toujours quelque chose, mais si ce quelque chose est 0 !

Donnez nous un jeu d'essai avec le résultat attendu (et expliqué), ce sera certainement plus clair...

[witch]

Salut,

Envoyé par aieeeuuuuu

Heu... pas très clair en effet.

Dans un premier temps, votre predicat EXISTS sera toujours vrai, puisque COUNT(*) retourne toujours quelque chose, mais si ce quelque chose est 0 !

Donnez nous un jeu d'essai avec le résultat attendu (et expliqué), ce sera certainement plus clair...

Oui en effet tu as raison, alors le résultat attendu doit être des fiches avec un Code =1 par exemple sur la table IMPORT et dont tous les enregistrements en rapport sur la table Client ont une valeur Null ( je parle de tous les enregistrements et pas seulement quelques uns)
C'est plus clair ?

[witch]

Je me débrouille pour l'instant avec cette requête :

Code :

1
2
3
4
5
6
7
8
SELECT date_import ,CODE,count(*) FROM IMPORT I1
WHERE indice >=2952868 AND 
EXISTS
( SELECT  *  FROM client WHERE  
( SELECT  count(*) FROM client WHERE indice IN (SELECT indice FROM IMPORT I2 WHERE I1.CODE =I2.CODE)  )  =  ( 
SELECT  count(*) FROM client WHERE  STATUS IS NULL AND indice IN (SELECT indice FROM IMPORT I2 WHERE I1.CODE =I2.CODE)   )  
 )
GROUP BY date_import, CODE ORDER BY date_import,CODE

je sais elle n'est pas très optimisée, ça prend environ 2 minutes 14

Sachant que le champ Indice est bien indexé.

Une requête plus optimisée ?

[aieeeuuuuu]

que donne ceci

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
SELECT 
	date_import ,
	CODE,
	count(*) 
FROM IMPORT I1
WHERE indice >=2952868 
AND NOT EXISTS (
	SELECT *
	FROM Client C
	WHERE C.indice = I1.indice
	AND STATUS IS NOT NULL
)
GROUP BY 
	date_import, 
	CODE 
ORDER BY 
	date_import,
	CODE

[witch]

aie, aie :

J'ai fais une modifs mais ça a pris 4 minutes, du coup je l'ai arrêté vu que je travaille sur des tables réelles, je promets de ne plus m'amuser sur ça (d'habitude quand ça arrive on commence à réclamer des problèmes sur la platforme utilisant ces tables )

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
SELECT 
    date_import ,
    CODE,
    count(*) 
FROM IMPORT I1
WHERE indice >=2952868 
AND NOT EXISTS ( ---ceux pour qui il n'existe aucun enregistrement dont le status n'est pas null 
    SELECT *
    FROM Client C
    WHERE --C.indice = I1.indice
        C.indice IN (SELECT indice FROM IMPORT I2  WHERE I1.CODE=I2.CODE ) 
    AND STATUS IS NOT NULL
)
GROUP BY 
    date_import, 
    CODE 
ORDER BY 
    date_import,
    CODE

Sinon sans ça, le fait que ça renvoie, comme j'ai cité "ceux pour qui il n'existe aucun enregistrement dont le status n'est pas null"
ceux ci tout en respectant la condition C.indice = I1.indice, sans prendre considération de la valeur CODE, alors que c'est en fonction de celle ci qu'il faut sortir ces enregistrements, tu vois?

[aieeeuuuuu]

Envoyé par witch

tu vois?

non !

et c'est pour ça que je demandais un jeu d'essai avec le résultat attendu :
deux petits tableaux, et on a tout compris (ou presque) !

[witch]

Envoyé par aieeeuuuuu

non !

et c'est pour ça que je demandais un jeu d'essai avec le résultat attendu :
deux petits tableaux, et on a tout compris (ou presque) !

Ok, pas de problème


On a une table IMPORT avec enregistrements :

INDICE DATE_IMPORT CODE
----------------------------
5000 date_quelconque 1321
5001 date_quelconque 1321
5002 date_quelconque 1321
5003 date_quelconque 5231
5004 date_quelconque 5231

la table CLIENT

INDICE STATUS
---------------
5000 4
5001 NULL
5002 5
5003 NULL
5004 NULL

Supposant que c'est tout ce qu'on a sur les deux tables.

Le requête doit m'envoyer uniquement les enregistrements du code 5231, ceux ci sont les seuls qui respectent la condition (tous les enregistrement en rapport sur la table CLIENT ont la valeur du status NULL)
la requête que tu as essayé renvoie même le code 1321.

En effet ça aurait été plus simple comme ça =)

[aieeeuuuuu]

ok

que donne cette requête alors :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
SELECT 
	date_import ,
	CODE,
	count(*) 
FROM IMPORT I1
WHERE indice >=2952868 
AND NOT EXISTS (
	SELECT *
	FROM Import I2
        INNER JOIN Client C
            ON I2.indice = C.indice
	WHERE I2.code = I1.code
	    AND STATUS IS NOT NULL
)
GROUP BY 
	date_import, 
	CODE 
ORDER BY 
	date_import,
	CODE

[witch]

Envoyé par aieeeuuuuu

ok

que donne cette requête alors :

Code :

Le résultat est correct (t'as eu tes 20/20 )

Mais sans la modif que j'ai fais (en gras), la requête prend des dizaines de minutes pour s'exécuter, en utilisant le champ indice qui est indexé, le temps d'exécution est diminué à 21 secondes, j'ai plus de 3 millions d'enregistrements sur cette table

Ta requête est bien plus optimisée que la mienne

Merci

[iberserk]

Je pense qu'une vue indexée peut grandement améliorer les performances:

Code :

1
2
3
4
CREATE VIEW dbo.V_CLIENT_CODE WITH SCHEMABINDING AS
SELECT CODE,COUNT_BIG(STATUS) AS STATUT
FROM dbo.CLIENT 
GROUP BY CODE

Avec un UNIQUE INDEX CLUSTERED sur Code...

Pouvez vous tester?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
SELECT 
    date_import ,
    CODE,
    count(*) 
FROM IMPORT I
   INNER JOIN  dbo.V_CLIENT_CODE V WITH(NO EXPAND)
      ON V.CODE=I.CODE
WHERE V.STATUS=0
             AND indice >=2952868 
GROUP BY 
    date_import, 
    CODE 
ORDER BY 
    date_import,
    CODE

[Waldar]

On peut jouer sur le fait que compter une colonne ne compte pas les nulls.
Sur votre exemple, ça fonctionne bien :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
; WITH IMPORT (INDICE, CODE) AS
(
SELECT 5000, 1321 union ALL
SELECT 5001, 1321 union ALL
SELECT 5002, 1321 union ALL
SELECT 5003, 5231 union ALL
SELECT 5004, 5231
)
   ,   CLIENT (INDICE, STATUS) AS
(
SELECT 5000, 4    union ALL
SELECT 5001, NULL union ALL 
SELECT 5002, 5    union ALL
SELECT 5003, NULL union ALL
SELECT 5004, NULL
)
  SELECT I.CODE
    FROM IMPORT AS I
         INNER JOIN CLIENT AS C
           ON C.INDICE = I.INDICE
   WHERE I.INDICE >= 5000
GROUP BY I.CODE
  HAVING count(C.STATUS) = 0
 
CODE
-----------
5231

[witch]

Bonjour,

Envoyé par iberserk

Je pense qu'une vue indexée peut grandement améliorer les performances:

Code :

Avec un UNIQUE INDEX CLUSTERED sur Code...

Pouvez vous tester?

Code :

Sur l'exemple que j'avais indiqué le champ CODE n'existe pas sur les deux tables, mais uniquement sur la table IMPORT, ça changerai tout pour la requête, avec la vue, cette méthode je n'avais encore jamais utilisé (peux tu m'expliquer la vue? )

On peut jouer sur le fait que compter une colonne ne compte pas les nulls.
Sur votre exemple, ça fonctionne bien :

Je ne comprends pas comment ça peut marcher, comment un

Code :

HAVING count(C.STATUS) = 0

peut remplacer pour un code dont tous les enregistrements en rapport sur la table client ont le STATUS null?

(je n'ai pas pu tester puisque la syntaxe de l'exemple ne tient pas sur sql server )

[Waldar]

Envoyé par witch

Je ne comprends pas comment ça peut marcher, comment un

Code :

HAVING count(C.STATUS) = 0

peut remplacer pour un code dont tous les enregistrements en rapport sur la table client ont le STATUS null?

Comme écrit, je m'appuis sur le fait que la fonction count(*) ne comptabilise pas les valeurs nulles.
Regardez, un exemple très simple :

Code :

1
2
3
4
5
6
7
SELECT count(col) AS cnt_col
     , count(*)   AS cnt_row
  FROM (SELECT cast(NULL AS int) AS col) AS sr
 
cnt_col     cnt_row
----------- -----------
0           1

Donc à partir du moment où un statut est non-null, le count(*) renverra un résultat strictement supérieur à 0.

Envoyé par witch

je n'ai pas pu tester puisque la syntaxe de l'exemple ne tient pas sur sql server

C'est pourtant bien du SQL-Server 2005.
Vous êtes peut-être en 2000 ?
Si oui, je vous donne l'équivalent :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
  SELECT I.CODE
    FROM (SELECT 5000 AS INDICE, 1321 AS CODE union ALL
          SELECT 5001          , 1321         union ALL
          SELECT 5002          , 1321         union ALL
          SELECT 5003          , 5231         union ALL
          SELECT 5004          , 5231) AS I
         INNER JOIN 
         (SELECT 5000 AS INDICE, 4   AS STATUS union ALL
          SELECT 5001          , NULL          union ALL 
          SELECT 5002          , 5             union ALL
          SELECT 5003          , NULL          union ALL
          SELECT 5004          , NULL) AS C
           ON C.INDICE = I.INDICE
   WHERE I.INDICE >= 5000
GROUP BY I.CODE
  HAVING count(C.STATUS) = 0

L'idée c'est juste de reprendre les données que vous avez communiquées un peu plus haut.

[iberserk]

Comme écrit, je m'appuis sur le fait que la fonction count(*) ne comptabilise pas les valeurs nulles.

C'était également l'idée de ma vue...

[witch]

Parfait

Merci

[Waldar]

Comment est-ce que ça répond en temps d'exécution en comparaison avec les autres requêtes ?

Et vous êtes bien en SQL-Server 2000 ?

[witch]

Bonsoir,

Envoyé par Waldar

Comment est-ce que ça répond en temps d'exécution en comparaison avec les autres requêtes ?

Et vous êtes bien en SQL-Server 2000 ?

Oui en effet c'est bien SQL-Server 2000 que j'utilise, Je n'ai pas ceci en main tout de suite, mais tout à l'heure quand j'avais testé sur des enregistrements réels comme j'avais fais avec la requête de aieeeuuuuu ça a pris 20 secondes au lieu de 21, pas trop de différence, mais ça reste important quand même.

Bonne soirée

[iberserk]

Sur l'exemple que j'avais indiqué le champ CODE n'existe pas sur les deux tables, mais uniquement sur la table IMPORT, ça changerai tout pour la requête, avec la vue, cette méthode je n'avais encore jamais utilisé (peux tu m'expliquer la vue? )

Le but est de créer une vue qui fait déjà l'agregat (GROUP BY)
En l'indexant (une vue indexée est une vue marquée avec le mot clez WITH SCHEMABINDING et sur laquelle vous créez un INDEX UNIQUE CLUSTER ce qui matérialise physiquement la base 'dans l'index')

Donc c'est comme si vous travaillez sur une table beaucoups plus petites...

Testez dans votre cas le gain peut être enorme...

[Waldar]

Les vues indexées en SQL-Server 2000 je n'y crois pas trop !