Discussion :

[oadin]

Bonjour,

Je suppose que cette réponse se trouve dans la FAQ, sur le forum et aussi dans les livres que je possède, veuillez m'excuser donc de ce message... je n'ai jamais trouvé les bons critères de recherche pour retrouver ce que je désirais. La preuve je ne savais pas quoi mettre en titre.

En fait ma requète "parait" fort simple.

Des tables avec des numéros.

Exemple :

1
2
4
5
8
20
21
22


Objectif de la requète renvoyé les numéros manquants.
Dans notre cas :

3
6
7
9
10
11
12
13
14
15
16
17
18
19

Solution qui a été choisie pour le moment (pas par moi) et qui me déplait est de créer une table avec des millions d'enregistrement et de faire un exists sur la table que l'on veut vérifier et notre table de numéro.

J'ai pensé a faire une boucle mais ça va être long (que ce soit du coté sql ou client). J'avais pensé à faire une requète récursive mais je n'arrive pas a l'écrire. (je suis bloqué car je peux parfois avoir un écart de plus de 100 chiffres et donc j'avais plus de 100 occurences).
J'ai écarté la table temporaire faisant la même chose que notre table actuel.... c'est lent pour faire pareil, je gagne juste de la place dans ma db.

C'est un problème pourtant tellement élémentaire que j'ai honte de ne pas trouver.

Merci de votre aide

[jero44]

Bonjour,

De mon coté je passerai par une procédure stockée pour résoudre le problème.
Par exemple pour une table:

Code :

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

create table t1(c1 int);

On définit la procédure suivante pour identifier les numéros non utilisés

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
create procedure manquant as
begin
  declare @valmax int;
  declare @i int;
  select @valmax=max(c1) from t1;
  set @i=1;
  while (@i<@valmax) begin
	if (not exists(select * from t1 where c1=@i)) begin
		print @i;
	end;
	set @i=@i+1
  end;
end;

[oadin]

Bonjour,

Oui c'est une des solutions que je cite dans mon premier message, mais que je fasse ma boucle du coté client ou sql, c'est très certainement plus lent que mon select * from numero where not exists (select 1 .....) and num < (select max(....))

[elsuket]

Oui c'est une des solutions que je cite dans mon premier message, mais que je fasse ma boucle du coté client ou sql, c'est très certainement plus lent que mon select * from numero where not exists (select 1 .....) and num < (select max(....))

J'en mettrai pas ma main à couper !
Je ne crois que les chiffres : exécutez les deux requêtes avec les options de session SET STATISTICS IO ON et SET STATISTICS TIME ON, puis comparez.

Solution qui a été choisie pour le moment (pas par moi) et qui me déplait est de créer une table avec des millions d'enregistrement et de faire un exists sur la table que l'on veut vérifier et notre table de numéro.

Cette table peut vous permettre de rendre votre requête très performante, comme une table de dates.
Ce sont des tables qui n'ont rien à faire d'un schéma (bon, et alors ?) mais qui peuvent s'avérer être très utiles : il vous suffit d'écrire une requête avec le prédicat EXCEPT et le tour est joué !
Vous avez alors une requête ensembliste, un temps d'exécution correct.

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
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Table des nombres de 0 à 100
CREATE TABLE TbNombres
(
	nombre INT
)
 
DECLARE @i INT SET @i = 1
WHILE @i <= 100
BEGIN
	INSERT INTO dbo.TbNombres VALUES (@i)
	SET @i = @i + 1
END
 
-- Talbe représentant la requête extrayant les nombres 
CREATE TABLE TbValeurs
(
	valeur INT
)
 
INSERT INTO dbo.TbValeurs VALUES(1)
INSERT INTO dbo.TbValeurs VALUES(2)
INSERT INTO dbo.TbValeurs VALUES(4)
INSERT INTO dbo.TbValeurs VALUES(5)
INSERT INTO dbo.TbValeurs VALUES(8)
INSERT INTO dbo.TbValeurs VALUES(20)
INSERT INTO dbo.TbValeurs VALUES(21)
INSERT INTO dbo.TbValeurs VALUES(22)
GO
 
-- Requête finale 
	SELECT nombre
	FROM dbo.TbNombres N
	JOIN dbo.TbValeurs V ON N.Nombre <= V.Valeur
EXCEPT
	SELECT valeur
	FROM dbo.TbValeurs

c'est lent pour faire pareil

Vrai

je gagne juste de la place dans ma db

Une table temporaire est .. une table et est donc stockée en tant que telle dans la base de données TempDB, dont SQL Server se sert massivement pour bien d'autres choses ...
Conséquence : si votre table temporaire est une # ou ##, vous demandez à SQL Server de maintenir des statistiques en plus de celles qu'il maintient dans les autres bases, de faire grossir le fichier, ...
Pour vous en convaincre, créez une table ou une variable de type table, sans même la peupler, et regardez la node "Tables temporaires" de TempDB :

Code :

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

1
2
3
4
5
6
DECLARE @TbToto TABLE
(
	val INT
)
 
WAITFOR DELAY '00:00:10'

Puis

Code :

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

CREATE TABLE #TOTO (val INT)

Si votre table temporaire est en fait une variable de type TABLE, elle est toujours stockée en tant que telle dans TempDB, mais SQL Server ne maintient aucune statistique, donc si elle contient beaucoup de lignes (et pire, de colonnes), bonjour les dégâts chez les performances.

C'est un problème pourtant tellement élémentaire que j'ai honte de ne pas trouver.

Quelle honte y-a-t-il ? tous les problèmes ne sont pas simples.
Le problème des non-équi-jointures est un problème récurrent

@++

[Antoun]

Solution qui a été choisie pour le moment (pas par moi) et qui me déplait est de créer une table avec des millions d'enregistrement et de faire un exists sur la table que l'on veut vérifier et notre table de numéro.

J'ai pensé a faire une boucle mais ça va être long (que ce soit du coté sql ou client). J'avais pensé à faire une requète récursive mais je n'arrive pas a l'écrire. (je suis bloqué car je peux parfois avoir un écart de plus de 100 chiffres et donc j'avais plus de 100 occurences).
J'ai écarté la table temporaire faisant la même chose que notre table actuel.... c'est lent pour faire pareil, je gagne juste de la place dans ma db.

C'est un problème pourtant tellement élémentaire que j'ai honte de ne pas trouver.

C'est un classique mais pas du tout élémentaire, dont la solution est effectivement de créer une table contenant les éléments à rechercher (à savoir une liste de nombre). Le EXCEPT ou la jointure externe peut être plus efficace que le EXISTS, mais fondamentalement ça revient au même, et ce sera certainement plus efficace qu'utiliser un curseur.

Enfin, pour la fameuse table avec des millions de lignes, voici une méthode ensembliste de génération. Tu crées une table contenant les chiffres de 0 à 9 :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE Chiffres(Num INT) ;
INSERT INTO Chiffres(Num) VALUES(0) ;
INSERT INTO Chiffres(Num) VALUES(1) ;
INSERT INTO Chiffres(Num) VALUES(2) ;
INSERT INTO Chiffres(Num) VALUES(3) ;
INSERT INTO Chiffres(Num) VALUES(4) ;
INSERT INTO Chiffres(Num) VALUES(5) ;
INSERT INTO Chiffres(Num) VALUES(6) ;
INSERT INTO Chiffres(Num) VALUES(7) ;
INSERT INTO Chiffres(Num) VALUES(8) ;
INSERT INTO Chiffres(Num) VALUES(9) ;

Ensuite tu n'as plus qu'à faire des produits cartésiens pour générer ta table ou vue de (par exemple) 10 millions de lignes en un temps record :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT U.Num + 10 * D.Num + 100 * C.Num + 1000 * M.Num + 
  10000 * DM.Num + 100000 * CM.Num + 1000000 * Mi.Num 
FROM Chiffres U -- unités
  CROSS JOIN Chiffres D -- dizaines
  CROSS JOIN Chiffres C  -- centaines
  CROSS JOIN Chiffres M  -- milliers
  CROSS JOIN Chiffres DM -- dizaines de milliers
  CROSS JOIN Chiffres CM -- centaines de milliers
  CROSS JOIN Chiffres Mi -- millions