Bonjour,

pour un de mes clients…

Nous avons effectué de nombreux tests pour une requête particulière qui consiste à rechercher toutes les données jointes ou non en raison d'une valeur NULL, à l'exception des données n'ayant aucune valeur correspondante explicite. C'est une sorte de semi anti-jointure.

Voici un exemple:

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
CREATE TABLE T_DATA
(DATA_ID            INT IDENTITY PRIMARY KEY,
 DATA_LIB           VARCHAR(32),
 REF_ID             INT)
GO
 
CREATE TABLE T_REF
(REF_ID            INT IDENTITY PRIMARY KEY,
 REF_LIB           VARCHAR(32))
GO
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
SET IDENTITY_INSERT T_DATA ON;
INSERT INTO T_DATA (DATA_ID, DATA_LIB, REF_ID) VALUES
(1, 'blabla', 10), 
(2, 'blibli', 10), 
(3, 'bleble', 11),
(4, 'bloblo', 12),
(5, 'blublu', NULL);
SET IDENTITY_INSERT T_DATA OFF;
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
SET IDENTITY_INSERT T_REF ON;
INSERT INTO T_REF (REF_ID, REF_LIB) VALUES
(10, 'ValA'),
(11, 'ValA'),
(99, 'ValA');
SET IDENTITY_INSERT T_REF OFF;
Le but est de retrouver les données suivantes :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
DATA_ID   DATA_LIB   REF_ID
--------- ---------- ----------
1         blabla     10
2         blibli     10
3         bleble     11
5         blublu     NULL

Voici les requêtes que nous avons testées :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
-- LG
SELECT D.* 
FROM   dbo.T_DATA AS D
       LEFT JOIN dbo.T_REF AS R
          ON D.REF_ID = R.REF_ID
WHERE  (D.REF_ID IS NULL AND R.REF_ID IS NULL)
       OR (D.REF_ID IS NOT NULL AND R.REF_ID IS NOT NULL);

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
-- NEW1
SELECT D.* 
FROM   dbo.T_DATA AS D
       FULL OUTER JOIN dbo.T_REF AS R
          ON  D.REF_ID= R.REF_ID
WHERE  (R.REF_ID IS NOT NULL OR (R.REF_ID IS NULL AND D.REF_ID IS NULL))
  AND  D.DATA_ID IS NOT NULL;
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
-- NEW2
SELECT D.* 
FROM   dbo.T_DATA AS D
       INNER JOIN dbo.T_REF AS R
          ON  R.REF_ID= D.REF_ID
UNION ALL
SELECT D.* 
FROM   dbo.T_DATA AS D
       FULL OUTER JOIN dbo.T_REF AS R
          ON D.REF_ID = R.REF_ID
WHERE  R.REF_ID IS NULL AND D.REF_ID IS NULL;
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
-- NEW3
SELECT D.* 
FROM   dbo.T_DATA AS D
       INNER JOIN dbo.T_REF AS R
          ON  R.REF_ID= D.REF_ID
UNION ALL
SELECT *
FROM   dbo.T_DATA AS D
WHERE  REF_ID IS NULL;
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
-- NEW4
SELECT D.* 
FROM   dbo.T_DATA AS D
       FULL OUTER JOIN dbo.T_REF AS R
          ON D.REF_ID = R.REF_ID
EXCEPT
SELECT D.* 
FROM   dbo.T_DATA AS D
WHERE  D.REF_ID IS NOT NULL AND NOT EXISTS(SELECT * 
                                           FROM   T_REF AS R 
                                           WHERE  R.REF_ID = D.REF_ID);
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
-- NEW5
SELECT D.* 
FROM   dbo.T_DATA AS D
       LEFT OUTER JOIN dbo.T_REF AS R
          ON D.REF_ID = R.REF_ID
WHERE  D.REF_ID IS NULL 
   OR  NOT EXISTS(SELECT *
                  FROM    dbo.T_REF AS R2
                  WHERE   R2.REF_ID = D.REF_ID);
Pour un volume de données approchant la réalité :

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
16
17
18
TRUNCATE TABLE T_REF;
TRUNCATE TABLE T_DATA;
 
INSERT INTO T_REF 
SELECT REPLICATE(CHAR(CHECKSUM(NEWID()) % 52 + 65), 32)
GO 100
 
INSERT INTO T_DATA 
SELECT REPLICATE(CHAR(CHECKSUM(NEWID()) % 52 + 65), 32), 1 + ABS(CHECKSUM(NEWID()) % 100);
GO 100000
 
INSERT INTO T_DATA 
SELECT REPLICATE(CHAR(CHECKSUM(NEWID()) % 52 + 65), 32), 101 + ABS(CHECKSUM(NEWID()) % 100);
GO 10000
 
INSERT INTO T_DATA 
SELECT REPLICATE(CHAR(CHECKSUM(NEWID()) % 52 + 65), 32), NULL;
GO 5000
Bilan LG est la meilleure sans index, NEW3 la meilleure avec index.

L'index :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
CREATE NONCLUSTERED INDEX X_DATA_REF
ON dbo.T_DATA (REF_ID)
INCLUDE (DATA_LIB)

D'autres suggestions ????

D'avance merci

A +