T-SQL : MSSQL 2016 - Abfrage optimieren - Doppelte Datensätze finden und löschen

[JoeG]

Hallo Gemeinde,
als Anwender habe ich eine Access Datenbank bekommen und soll sehen inwieweit ich sie optimieren kann, konkret doppelte Datensätze finden und löschen.

Folgende Abfrage habe ich schon mit dem Assistenten generiert:

Code:

SELECT
          fstbetwuerfEN.[beprauftr_nr            ]
        , fstbetwuerfEN.[fstbetwuerf_pruefalt    ]
        , fstbetwuerfEN.[fstbetwuerf_a           ]
        , fstbetwuerfEN.[fstbetwuerf_masse       ]
        , fstbetwuerfEN.[fstbetwuerf_brlast      ]
        , fstbetwuerfEN.[fstbetwuerf_rohdi       ]
        , fstbetwuerfEN.[fstbetwuerf_festkt      ]
        , fstbetwuerfEN.[fstbetwuerf_fribetprob  ]
        , fstbetwuerfEN.[fstbetwuerf_nr          ]
        , fstbetwuerfEN.[fstbetwuerf_kdbez       ]
        , fstbetwuerfEN.[fstbetwuerf_labbez      ]
        , fstbetwuerfEN.[fstbetwuerf_grundmass   ]
        , fstbetwuerfEN.[fstbetwuerf_pruefdatfri ]
        , fstbetwuerfEN.[fstbetwuerf_pruefdat    ]
        , fstbetwuerfEN.[fstbetwuerf_b           ]
        , fstbetwuerfEN.[fstbetwuerf_h           ]
        , fstbetwuerfEN.[fstbetwuerf_d           ]
        , fstbetwuerfEN.[fstbetwuerf_prueftyp    ]
        , fstbetwuerfEN.[fstbetwuerf_uebertr     ]
        , fstbetwuerfEN.[fstbetwuerf_formtyp     ]
        , fstbetwuerfEN.[fstbetwuerf_pruefkoerp  ]
        , fstbetwuerfEN.[fstbetwuerf_brlast_bz   ]
        , fstbetwuerfEN.[fstbetwuerf_festkt_bz   ]
        , fstbetwuerfEN.[fstbetwuerf_wass_eindr  ]
        , fstbetwuerfEN.[fstbetwuerf_druckfl     ]
        , fstbetwuerfEN.[fstbetwuerf_wf15_korr   ]
        , fstbetwuerfEN.[fstbetwuerf_nr_ref      ]
        , fstbetwuerfEN.[fstbetwuerf_pruefer     ]
        , fstbetwuerfEN.[fstbetwuerf_pruefalt_std]
        , fstbetwuerfEN.[fstbetwuerf_pruefk_nr   ]
        , fstbetwuerfEN.[fstbetwuerf_ausreisser  ]
        , fstbetwuerfEN.[fstbetwuerf_pruefzeitfri]
        , fstbetwuerfEN.[fstbetwuerf_pruefzeit   ]
        , fstbetwuerfEN.[fstbetwuerf_spaltzug    ]
        , fstbetwuerfEN.[fstbetwuerf_emodul      ]
        , fstbetwuerfEN.[fstbetwuerf_pk_fremd    ]
        , fstbetwuerfEN.[fstbetwuerf_anz_last    ]
FROM
          fstbetwuerfEN
WHERE
          (
                    (
                              (
                                        fstbetwuerfEN.[beprauftr_nr]
                              )
                              IN
                              (
                                        SELECT
                                                  [beprauftr_nr]
                                        FROM
                                                  [fstbetwuerfEN] AS Tmp
                                        GROUP BY
                                                  [beprauftr_nr]
                                                , [fstbetwuerf_pruefalt]
                                                , [fstbetwuerf_a]
                                                , [fstbetwuerf_masse]
                                                , [fstbetwuerf_brlast]
                                                , [fstbetwuerf_rohdi]
                                                , [fstbetwuerf_festkt]
                                                , [fstbetwuerf_fribetprob]
                                        HAVING
                                                  COUNT(*)                     >1
                                                  AND [fstbetwuerf_pruefalt]   = [fstbetwuerfEN].[fstbetwuerf_pruefalt]
                                                  AND [fstbetwuerf_a]          = [fstbetwuerfEN].[fstbetwuerf_a]
                                                  AND [fstbetwuerf_masse]      = [fstbetwuerfEN].[fstbetwuerf_masse]
                                                  AND [fstbetwuerf_brlast]     = [fstbetwuerfEN].[fstbetwuerf_brlast]
                                                  AND [fstbetwuerf_rohdi]      = [fstbetwuerfEN].[fstbetwuerf_rohdi]
                                                  AND [fstbetwuerf_festkt]     = [fstbetwuerfEN].[fstbetwuerf_festkt]
                                                  AND [fstbetwuerf_fribetprob] = [fstbetwuerfEN].[fstbetwuerf_fribetprob]
                              )
                    )
          )
ORDER BY
          fstbetwuerfEN.[beprauftr_nr]
        , fstbetwuerfEN.[fstbetwuerf_pruefalt]
        , fstbetwuerfEN.[fstbetwuerf_a]
        , fstbetwuerfEN.[fstbetwuerf_masse]
        , fstbetwuerfEN.[fstbetwuerf_brlast]
        , fstbetwuerfEN.[fstbetwuerf_rohdi]
        , fstbetwuerfEN.[fstbetwuerf_festkt]
        , fstbetwuerfEN.[fstbetwuerf_fribetprob]
;

fstbetwuerf_festkt], fstbetwuerfEN.[fstbetwuerf_fribetprob];

Dabei kommt dann folgendes raus:

Code:

beprauftr_nr	fstbetwuerf_nr	fstbetwuerf_kdbez	fstbetwuerf_labbez	fstbetwuerf_pruefalt	fstbetwuerf_grundmass	fstbetwuerf_pruefdatfri	fstbetwuerf_pruefdat	fstbetwuerf_a	fstbetwuerf_b	fstbetwuerf_h	fstbetwuerf_d	fstbetwuerf_masse	fstbetwuerf_brlast	fstbetwuerf_prueftyp	fstbetwuerf_uebertr	fstbetwuerf_formtyp	fstbetwuerf_pruefkoerp	fstbetwuerf_fribetprob	fstbetwuerf_brlast_bz	fstbetwuerf_rohdi	fstbetwuerf_festkt	fstbetwuerf_festkt_bz	fstbetwuerf_wass_eindr	fstbetwuerf_druckfl	fstbetwuerf_wf15_korr	fstbetwuerf_nr_ref	fstbetwuerf_pruefer	fstbetwuerf_pruefalt_std	fstbetwuerf_pruefk_nr	fstbetwuerf_ausreisser	fstbetwuerf_pruefzeitfri	fstbetwuerf_pruefzeit
PLC38326401	4	               	L38326401-14   	 56	150	12.01.2011	09.03.2011	150	150	150		7,93	1093	1	FALSCH	1	1	L38326401-1		2,34	48,6			0	1,00		PLC38326401		001221OC-4	FALSCH	07:59	00:00
PLC38326401	5	               	L38326401-15   	 56	150	12.01.2011	09.03.2011	150	150	150		7,93	1093	1	FALSCH	1	1	L38326401-1		2,34	48,6			0	1,00		PLC38326401		001221OC-5	FALSCH	07:59	00:00
PLC39842687	2	               	L39842687-12   	 28	150	04.01.2011	01.02.2011	150	150	150		7,89	991	1	FALSCH	1	1	L39842687-1		2,33	44			0	1,00		PLC39842687		002489OC-2	FALSCH	11:28	00:00
PLC39842687	3	               	L39842687-13   	 28	150	04.01.2011	01.02.2011	150	150	150		7,89	991	1	FALSCH	1	1	L39842687-1		2,33	44			0	1,00		PLC39842687		002489OC-3	FALSCH	11:28	00:00
PLC39842687	4	               	L39842687-14   	 56	150	04.01.2011	01.03.2011	150	150	150		7,86	1129	1	FALSCH	1	1	L39842687-1		2,32	50,2			0	1,00		PLC39842687		002489OC-4	FALSCH	11:28	00:00
PLC39842687	5	               	L39842687-15   	 56	150	04.01.2011	01.03.2011	150	150	150		7,86	1129	1	FALSCH	1	1	L39842687-1		2,32	50,2			0	1,00		PLC39842687		002489OC-5	FALSCH	11:28	00:00
PLC40533624	1	               	L40533624-11   	 28	150	14.10.2014	11.11.2014	151	150	150		7,9	1301	1	FALSCH	1	1	L40533624-1		2,32	57,4			0	1,00		PLC40533624		007325OC-1	FALSCH	13:31	00:00
PLC40533624	2	               	L40533624-12   	 28	150	14.10.2014	11.11.2014	151	150	150		7,9	1301	1	FALSCH	1	1	L40533624-1		2,32	57,4			0	1,00		PLC40533624		007325OC-2	FALSCH	13:31	00:00
PLC70237823	4	               	L70237823-14   	 56	150	05.01.2011	02.03.2011	150	150	150		7,84	1287	1	FALSCH	1	1	L70237823-1		2,32	57,2			0	1,00		PLC70237823		002489OC-4	FALSCH	09:33	00:00
PLC70237823	3	               	L70237823-13   	 56	150	05.01.2011	02.03.2011	150	150	150		7,84	1287	1	FALSCH	1	1	L70237823-1		2,32	57,2			0	1,00		PLC70237823		002489OC-3	FALSCH	09:33	00:00

Jetzt fehlt mir noch die Eingrenzung.

In der Spalte "fstbetwuerf_nr" gibt es eine aufsteigende Nummer. Es sollen die Datensätze stehen bleiben mit der kleinsten Nummer. Alle anderen (fast) doppelten Datensätze sollen gelöscht werden.

Wie muss die Abfrage lauten um diese Werte anzuzeigen/zu löschen?

VG
Joe

[hcscherzer]

In der Liste, die die Ergebnisse darstellt, sind ja neben der fstbetwuerf_nr auch noch die Daten in fstbetwuerf_kdbez unterschiedlich ... ist dies die Spalte mit dem Primärschlüssel?
Einen solchen braucht es nämlich um die zu löschenden Sätze eindeutig zu identifizieren ...

Angenommen, das wäre so und die obige Abfrage sei gespeichert unter dem Namen qry_mehrfache ...

Code:

delete from fstbetwuerfEN as f
 where f.fstbetwuerf_kdbez in (select fstbetwuerf_kdbez from qry_mehrfache)
 and f.fstbetwuerf_nr > (select min(fstbetwuerf_nr) from qry_mehrfache where beprauftr_nr = f.beprauftr_nr)

[JoeG]

Hallo und Danke Hans-Christian für die Antwort, Primärschlüssel sind "beprauftr_nr" und "fstbetwuerf_nr".

Wenn ich Deine Abfrage starte, passiert leider nichts, Access reagiert nicht mehr (keine Rückmeldung), so hab ich das jetzt schon 2 Stunden zu stehen. Im Taskmanager "arbeitet" der Prozess allerdings. Wie lange dauert denn Löschen bei ca 70.000 Datensätzen wovon ca 650 bei der Selektion gefunden werden (hab mir alles lokal auf die Festplatte geholt)?

[hcscherzer]

Zitat:

jetzt schon 2 Stunden zu stehen

Access ist da nicht besonders flott, wenn es mit Unterabfragen arbeiten muss.

Um mal zu sehen, ob überhautp was passiert, könntest Du ja mal das DELETE durch einen SELECT fstbetwuerf_nr ersetzen.
Und zusätzlich die 70.000 eingrenzen, indem Du Dir nur eine kleine Teilmenge auf die lokale Access Instanz kopierst.

Wenn Du die Möglichkeit hast, die Abfrage direkt auf dem SQL Server (im Management-Studio) zu starten, solltest Du es dort ausführen lassen.
Aber - vorsichtshalber - zuerst auch mit SELECT und dann mit DELETE.

[JoeG]

Das war es noch nicht.

Ich habe die Datensätze auf 3000 reduziert und mir wieder lokal gezogen.
Wenn ich jetzt den Befehl ausführe:

Code:

SELECT fstbetwuerf_nr from fstbetwuerfEN as f
 where f.fstbetwuerf_kdbez in (select fstbetwuerf_kdbez from qry_mehrfache)
 and f.fstbetwuerf_nr > (select min(fstbetwuerf_nr) from qry_mehrfache where beprauftr_nr = f.beprauftr_nr)

Da wird nichts gefunden.

Der "qry_mehrfache" findet aber ca 400 Datensätze.

VG
Joe

Edit:

Jetzt klappt es.

Ich habe f.fstbetwuerf_kdbez gegen f.fstbetwuerf_labbez getauscht und siehe da...ich denke es läuft so wie Du es angedacht hast:

Code:

delete from fstbetwuerfEN as f
 where f.fstbetwuerf_labbez in (select fstbetwuerf_labbez from qry_mehrfache)
 and f.fstbetwuerf_nr > (select min(fstbetwuerf_nr) from qry_mehrfache where beprauftr_nr = f.beprauftr_nr)

Vlt kannst Du das noch kurz bestätigen, bevor ich das im Produktivsystem loslasse, aber für mich scheint es plausibel.

[hcscherzer]

Wenn es denn läuft und wenn die Datensätze, die per SELECT angezeigt werden, genau diejenigen sind, die gelöscht werden sollen, dann kannst Du es loslassen.

So genau kenne ich Deine Struktur ja nicht, aber wenn es mit _labbez läuft statt mir _nr ... dann bestätige ich Dir das hiermit.
Ohne jede Verantwortung zu übernehmen.
Aber ein Backup machst Du ja sicher in jedem Fall vorher!?

[JoeG]

Super...

Backup erstellt und Delete gestartet. Der erste Blick sieht sehr gut aus.

Vielen Dank für Deine schnelle Hilfe!

VG
Joe