Récursivité et SQL

[invite8cef861c]

Bonjour,

J'ai besoin de votre aide pour une requête SQl, voici mon problème :
Je dispose d'une table T et trois colonnes A, B et C renseigné comme suis :
A B C
1 W NULL
1 X NULL
2 X NULL
2 Y NULL
2 Z NULL
3 M NULL
4 M NULL
5 M NULL
5 N NULL
6 O NULL

J'aimerai trouver un moyen de faire une requête d'update (sur la colonne C) pour grouper toutes les lignes qui peuvent avoir un lien (que se soit en passant par la colonne A ou B).
Plus clairement, j'aimerai avoir ce résultat :
A B C
1 W 1
1 X 1
2 X 1
2 Y 1
2 Z 1
3 M 2
4 M 2
5 M 2
5 N 2
6 O 3

Les lignes 1 et 2 sont liées par la colonne A, les lignes 2 et 3 sont liées par la colonne B et ainsi de suite, on continu jusqu'à ce qu'on en trouve plus.


- J'ai déjà trouvé une solution avec une fonction récursive en C#. Seulement le temps d’exécution laisse à désirer (la table sur laquelle je souhaite faire ça contient des millions de lignes)
- J'ai trouvé qu'il était possible en sql de faire de la récursivité. J'ai sorti ça mais ça ne fonctionne pas :

WITH 
   grappe (A, B, C, Level)
   AS (SELECT A, B, C, 0 as Level
       FROM T
	   WHERE C IS NULL

       UNION ALL

       SELECT t.A, t.B, t.C, Level +1
       FROM   T t
              INNER JOIN grappe u
                    ON t.A = u.A OR t.B = u.B)

UPDATE T SET C = Level FROM   grappe

Si il se trouve des pros de SQL dans les parages, ou si vous voulez relever le défi, je vous en serait très reconnaissant.

Merci à vous !!!
-----

[invite0bbe92c0]

Bonjour

Bonjour,

J'ai besoin de votre aide pour une requête SQl, voici mon problème :
Je dispose d'une table T et trois colonnes A, B et C renseigné comme suis :
- J'ai déjà trouvé une solution avec une fonction récursive en C#. Seulement le temps d’exécution laisse à désirer (la table sur laquelle je souhaite faire ça contient des millions de lignes)!

C'est très étonnant, à moins que tu considère une fonction en C# exécutée dans un contexte applicatif; car dans un contexte d'exécution SQLCLR, les fonctions et procédures écrites en C# ne sont pas fondamentalement moins performantes que les même écrites en T-SQL (c'est d'ailleurs le seul moyen d'écrire des fonctions d'agrégats, le T-SQL ne supportant pas l'écriture de fonctions d'agrégat utilisateurs contrairement au PL/SQL par exemple).

Sinon, ton problème est assez simple, mais ce serait mieux que tu postes le DDL de la table afin qu'on puisse tester la solution à te donner sans avoir à se faire ch..r à recréer la table.

[invite8cef861c]

Merci de ta réponse,

j'exécute ma fonction C# à partir d'une application console (j'imagine que c'est ce que tu entend par "contexte applicatif" ?) et je me dit que le lien entre mon application et la base peut être source de lenteur...

Pour ma table, voici le code :

CREATE TABLE [dbo].[T](
	[A] [nvarchar](50) NOT NULL,
	[B] [nvarchar](50) NOT NULL,
	[C] [nvarchar](50) NULL,
 CONSTRAINT [PK_T] PRIMARY KEY CLUSTERED 
(
	[A] ASC,
	[B] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

[invite0bbe92c0]

Merci de ta réponse,

j'exécute ma fonction C# à partir d'une application console (j'imagine que c'est ce que tu entend par "contexte applicatif" ?) et je me dit que le lien entre mon application et la base peut être source de lenteur...

Oui, c'est cela; et effectivement dans ce cas, c'est obligatoirement beaucoup moins performant (vraiment beaucoup) qu'une procédure exécutée dans le contexte du SGBD (et le concept de SQLCLR permet d'avoir des proc stoc et des fonctions écrites en C# directement sur la base, qui s'exécutent dans ce cas dans le contexte de l'instance Sql Server - si toute fois tu travailles avec SQL SERVER ce qui semble être le cas si j'en juge à certains éléments du DDL posté )

Tu confirmes que c'est bien du SQL Server ? Car, par exemple, la solution possible sur Oracle fait appel à des instructions spécifiques du SQL Oracle , CONNECT-BY & PRIOR, n'existant par sur SQL Server.

[A voir en vidéo sur Futura]

[invite8cef861c]

Je ne ssavias pas qu'on pouvait exécuter du C# dans Sql Server, je creuserais l'info. Merci !

Et je travaille bien sur SQL Server.

[invite8cef861c]

En avoir discuté avec toi, m'a ouvert de nouvelles portes :
J'ai trouvé une solution uniquement en T-SQL en utilisant un curseur, sans récursivité et donc beaucoup plus rapide que mon application (d'un facteur 6 !)

Pour ceux que ça pourrait intéresser :

ALTER PROCEDURE [dbo].[Prog]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @A nvarchar(50)
DECLARE @B nvarchar(50)
DECLARE @C nvarchar(50)
DECLARE @NB INT
SET @NB = 1

DECLARE Curs CURSOR FOR 
SELECT A, B FROM T WHERE C IS NULL
OPEN Curs
WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT @C = ISNULL(MAX(C), 0) FROM T WHERE (A = @A OR B = @B)
		IF (@C > 0)
		BEGIN
			UPDATE T SET C = @C FROM T WHERE (A = @A AND B = @B)
		END
		ELSE
		BEGIN
			UPDATE T SET C = @NB FROM T WHERE (A = @A AND B = @B)
			SET @NB = @NB + 1
		END
FETCH NEXT FROM Curs INTO @A, @B
END 
CLOSE Curs;
DEALLOCATE Curs;
END

Merci pour tes réponses Bluedeep.