Ermittlung rekursiver Verbindungen

[FischiFisch 10]

Hallo allerseits,

 

ich muss aus einem Datenbestand rekursive Verbindungen zwischen rund 3 Mio. Datensätzen ermitteln.

 

Die Quelltabelle beinhaltet 2 Nummern (Quelle und Ziel) - Beispiel:

1 - 2

1 - 3

2 - 4

2 - 5

3 - 1

3 - 4

6 - 7

7 - 8

9 - 1

Ziel ist es nun alle mit einander verbundenen Nummern mit einer gemeinsamen Nummer zu kennzeichnen, d.h.

1 - 2 = 01

1 - 3 = 01

2 - 4 = 01

2 - 5 = 01

3 - 1 = 01

3 - 4 = 01

6 - 7 = 02

7 - 8 = 03

9 - 1 = 01

Mein Problem ist die Rekursion (siehe 1 verbunden direkt mit 2,3 und über 2 mit 4,5 und über 3 mit 1,4). Für die Rekursionstiefe gibt es keine Beschränkung. Alle miteinander verbundenen Daten sollen nachher durch eine gemeinsame Nummer verbunden sein.

 

Hier mein derzeitiger Source-Code

 

ALTER PROCEDURE [dbo].sp_wolke --@intIdx INT = 0

 

AS

BEGIN

 

DECLARE @int_WOLKE INTEGER = 1

DECLARE @int_REKURSIONSTIEFE INTEGER = 1

DECLARE @int_LAUFEN_A INTEGER = 1

DECLARE @int_LAUFEN_B INTEGER = 1

DECLARE @int_AUFRAEUMEN INTEGER = 1

 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_TEMP_BEZIEHUNGEN_ERG]') AND type in (N'U'))

DROP TABLE tb_TEMP_BEZIEHUNGEN_ERG

SELECT * INTO tb_TEMP_BEZIEHUNGEN_ERG FROM tb_temp_beziehungen_test WHERE WOLKEN_NR > 0

WHILE @int_LAUFEN_A = 1

BEGIN

UPDATE tb_temp_beziehungen_test

SET WOLKEN_NR = @int_WOLKE

FROM tb_temp_beziehungen_test

WHERE Source_int = (SELECT TOP(1) Source_int FROM tb_temp_beziehungen_test WHERE WOLKEN_NR = 0)

IF @@ROWCOUNT = 0

BEGIN

SET @int_LAUFEN_A = 0

SET @int_LAUFEN_B = 0

END

ELSE

SET @int_LAUFEN_B = 1

 

WHILE @int_LAUFEN_B = 1

BEGIN

UPDATE tb_temp_beziehungen_test

SET WOLKEN_NR = @int_WOLKE,

REKURSION = @int_REKURSIONSTIEFE

FROM tb_temp_beziehungen_test x

WHERE WOLKEN_NR = 0 AND

EXISTS (SELECT 1 FROM tb_temp_beziehungen_test y

WHERE y.WOLKEN_NR = @int_WOLKE AND (x.Source_int = y.Dest_int OR x.Dest_int = y.Dest_int))

IF @@ROWCOUNT = 0

BEGIN

SET @int_LAUFEN_B = 0

SET @int_WOLKE = @int_WOLKE + 1

SET @int_AUFRAEUMEN = @int_AUFRAEUMEN + 1

SET @int_REKURSIONSTIEFE = 1

END

ELSE

SET @int_REKURSIONSTIEFE = @int_REKURSIONSTIEFE + 1

END

IF @int_AUFRAEUMEN > 1000

BEGIN

DELETE FROM tb_temp_beziehungen_test OUTPUT deleted.* INTO tb_TEMP_BEZIEHUNGEN_ERG WHERE WOLKEN_NR > 0

SET @int_AUFRAEUMEN = 1

END

END

DELETE FROM tb_temp_beziehungen_test OUTPUT deleted.* INTO tb_TEMP_BEZIEHUNGEN_ERG WHERE WOLKEN_NR > 0

END

 

 

Für 100.000 Quelldatensätze läuft diese Routine ca. 30 Minuten. Bei 3 Mio Datensätzen sind nach 12h noch nicht mal 40.000 Sätze verarbeitet.

 

Ich hoffe das alles ist soweit verständlich und über eine interessante Diskussion komme ich auf eine performante Lösung.

bearbeitet 20. Juni 2012 von FischiFisch