Discussion :

[SQLpro]

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 +

[StringBuilder]

Salut,

Je crois bien que c'est la première fois que je te vois poser une question ici

Rien trouvé de follichon, au mieux ça fait le même plan d'exécution...
En tout cas, sur mon PC je confirme l'ordre de rapidité avec ou sans index.

Sinon, à tout hasard...

Code sql :

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

1
2
delete dbo.T_DATA
where REF_ID is not null and REF_ID not in (select REF_ID from dbo.T_REF);

[StringBuilder]

Plus sérieusement, cette solution bien merdique me permet d'exploser tes deux requêtes quand il n'y a pas d'index :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
alter table T_DATA add ref_not_null as isnull(REF_ID, -1);
go
 
with ref (id)
as
(
	select -1
	union all
	select REF_ID from dbo.T_REF
)
select DATA_LIB, REF_ID
from dbo.T_DATA d
inner join ref on ref.id = ref_not_null;

Par contre avec cet index, je suis entre les deux...

Code sql :

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

1
2
3
4
 
CREATE NONCLUSTERED INDEX X_DATA_REF_NULL
ON dbo.T_DATA (ref_not_null)
INCLUDE (REF_ID, DATA_LIB)

-- Edit : quitte à la faire sans index, la colonne calculée ne sert à rien

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
 
with ref (id)
as
(
	select -1
	union all
	select REF_ID from dbo.T_REF
)
select DATA_LIB, REF_ID
from dbo.T_DATA d
inner join ref on ref.id = isnull(d.REF_ID, -1);

[aieeeuuuuu]

Bonjour,

Pour la version "LG", pourquoi ne pas simplifier le filtre ?

Code :

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

1
2
3
4
5
6
 
SELECT D.* 
FROM   dbo.T_DATA AS D
       LEFT JOIN dbo.T_REF AS R
          ON D.REF_ID = R.REF_ID
WHERE  R.REF_ID IS NOT NULL OR D.REF_ID IS NULL

[aieeeuuuuu]

pour la solution NEW3, à voir s'il n'est pas préférable de séparer l'index en deux indexes filtrés, l'un avec REF_ID à NULL, et l'autre à non NULL