Sehr langsame Abfrage LÖSCHEN

Habe ich Probleme mit der SQL-Leistung. Für plötzliche Grund der folgenden Abfragen sehr langsam:

Ich habe zwei Listen enthält die Id einer bestimmten Tabelle. Wie lösche ich alle Datensätze aus der ersten Liste, wenn die Id bereits vorhanden ist in der zweiten Liste:

DECLARE @IdList1 TABLE(Id INT)
DECLARE @IdList2 TABLE(Id INT)

-- Approach 1
DELETE list1
FROM @IdList1 list1
INNER JOIN @IdList2 list2 ON list1.Id = list2.Id

-- Approach 2
DELETE FROM @IdList1
WHERE Id IN (SELECT Id FROM @IdList2)

Ist es möglich, die zwei Listen enthält mehr als 10.000 Datensätze. In diesem Fall werden beide Abfragen dauert jeweils mehr als 20 Sekunden ausgeführt werden.

Den Ausführungsplan zeigte auch etwas, was ich nicht verstehe. Vielleicht erklärt warum es so langsam ist:
Sehr langsame Abfrage LÖSCHEN

Füllte ich die beiden Listen mit 10.000 sequenziell ganze zahlen, so dass beide Liste enthaltenen Wert 1-10.000 als Ausgangspunkt.

Wie Sie sehen können beide Abfragen zeigt für @IdList2 die Tatsächliche Anzahl von Zeilen ist 50.005.000!!. @IdList1 korrekt ist (die Tatsächliche Anzahl von Zeilen ist 10.000)

Ich weiß, es gibt andere Lösungen wie man dieses Problem lösen. Wie füllen Sie eine Dritte Liste nur das entfernen aus der ersten Liste. Aber meine Frage ist:

Warum sind diese delete-Anfragen so langsam und warum sehe ich diese seltsame Abfrage-Pläne?

Ist das ein problem, dass vielleicht auftreten in einem realen Szenario, oder, gerade in dieser specilaized situation?
Die zugrunde liegenden Probleme keine Statistiken kompiliert Sie für table-Variablen (und Mangel an nützlichen Indizes auf Ihnen) ist sehr Häufig.

InformationsquelleAutor hwcverwe | 2013-05-23

15

Fügen Sie einen Primärschlüssel für Ihre Tabelle-Variablen und sehen, wie Sie Schreien
```
DECLARE @IdList1 TABLE(Id INT primary Key not null)
DECLARE @IdList2 TABLE(Id INT primary Key not null)
```
weil es keinen index für diese Tabelle Variablen, joins oder Unterabfragen müssen prüfen, die auf der Größenordnung von 10.000 x 10.000 = 100,000,000 Paare von Werten.
- Wird es helfen, mit einem index auf @IdList1?
- "Alle joins oder Unterabfragen müssen prüfen, die auf der Größenordnung von 10.000 x 10.000 = 100,000,000 Paare von Werten." dies gilt nur für geschachtelte Schleifen. Ein hash oder merge join würde jede Eingabe nur einmal (wenn ein merge-join, wäre auch eine Sortierung)
- Ich habe nicht gelesen, dass die Sachen für eine Weile, so dass ich vergessen habe die Regeln, aber Ist es nicht die Auswahl der verschachtelten Schleifen, weil es keinen index? Zu tun, die andere looping-algorithmen nicht, es muss ein index zum Sortieren der Werte? Auch, ohne einen index, der es noch hat zu prüfen, jedes paar von Werten - egal, was looping-Algorithmus verwendet es, um Sie zu erstellen. - Ausnahme, Sie beachten ein merge-join, aber es hat zum Vorsortieren Sie.
- Nein, es können hash oder merge join, solange es ist ein equi-join. Merge join erfordert Sortieren Sie die beiden Eingänge (so wird das erstellen eines index), aber sobald ein index erstellt wurde, ist es offensichtlich potenziell nützlich, da Sie Vorteile für die anderen Abfragen (also +1)
- Ihre Antworten und Kommentare zusammen mit @MartinSmith war eine enorme Verbesserung. Danke!
InformationsquelleAutor Charles Bretana
12

SQL Server kompiliert den plan, wenn die table-variable leer ist und nicht neu kompilieren, wenn Zeilen Hinzugefügt werden. Versuchen
```
DELETE FROM @IdList1
WHERE Id IN (SELECT Id FROM @IdList2)
OPTION (RECOMPILE)
```
Dieser berücksichtigt die tatsächliche Anzahl der Zeilen in der Tabelle enthalten sind variable und loszuwerden, die nested loops-plan

Natürlich das erstellen eines index für Id über eine Einschränkung kann auch von Vorteil sein, für andere Abfragen über die Tabelle variable zu.
- Das ist mir neu. Können Sie das klären, - Den cacheplan ersten kompilieren passieren würde, wenn die Delete-Anweisung angetroffen wird, richtig? Nicht, wenn der table-Variablen deklariert sind ? Ich meine, der plan kompiliert wurde, ist für das Löschen, nicht die Tabelle Variablen-Deklaration... Wenn dem so ist, dann an diesem Punkt wäre nicht der table-Variablen gefüllt werden ? Auch, wenn es Ihnen nichts ausmacht, könnten Sie eine Referenz ? Ich möchte Lesen, bis auf diese.
- Es gibt einige links und Beispiel code in meine Antwort hier
- danke ... etwas Gelernt heute!
- wieder, jedoch, da die cacheplan erstellt für jede Aussage, nicht für das gesamte batch oder eine gespeicherte Prozedur, schafft es die cache-Pläne für jede Anweisung in einem batch oder in einer Prozedur, bevor es beginnt mit der Ausführung?
- Es kompiliert alle Anweisungen in einem batch sein, bevor Sie ausgeführt wird, außer wenn die Anweisung verweist auf ein nicht existentes Objekt und markiert für latente kompilieren. Also in diesem Fall die DELETE - Anweisung wird kompiliert, wenn die table-Variablen leer sind. Dann (aufgrund OPTION (RECOMPILE)) es wird neu kompiliert am Punkt der DELETE und können die Berücksichtigung der tatsächlichen Anzahl von Zeilen, nach der table-Variablen aufgefüllt werden.
- Ihre Antworten und Kommentare zusammen mit @CharlesBretana war eine enorme Verbesserung. Ich desiced zu akzeptieren Charels' beantworten, weil ich nicht akzeptieren kann zwei Antworten ;). Danke!
InformationsquelleAutor Martin Smith
2

Tabellen in die table-Variablen können primary keys, also, wenn Sie Ihre Daten unterstützt die Einzigartigkeit dieser Ids, können Sie in der Lage sein, um die Leistung zu verbessern, indem Sie für
```
DECLARE @IdList1 TABLE(Id INT PRIMARY KEY)
DECLARE @IdList2 TABLE(Id INT PRIMARY KEY)
```
InformationsquelleAutor AakashM
2

Mögliche Lösungen:

1) Versuchen Sie, erstellen Sie Indizes so

1.1), Wenn Liste{1/2}.Die Spalte Id hat einzigartige Werte, dann definieren Sie einen eindeutigen gruppierten index mit einem PK-Einschränkung wie diese:
```
DECLARE @IdList1 TABLE(Id INT PRIMARY KEY);
DECLARE @IdList2 TABLE(Id INT PRIMARY KEY);
```
1.2) Wenn Die Liste{1/2}.Id-Spalte kann mit doppelten Werten, dann definieren Sie einen eindeutigen gruppierten index mit einem PK-Einschränkung mit einem dummy IDENTITY Spalte wie folgt:
```
DECLARE @IdList1 TABLE(Id INT, DummyID INT IDENTITY, PRIMARY KEY (ID, DummyID) );
DECLARE @IdList2 TABLE(Id INT, DummyID INT IDENTITY, PRIMARY KEY (ID, DummyID) );
```
2) Versuchen Sie, fügen Sie HASH JOIN Abfrage Hinweis wie dieser:
```
DELETE list1
FROM @IdList1 list1
INNER JOIN @IdList2 list2 ON list1.Id = list2.Id
OPTION (HASH JOIN);
```
InformationsquelleAutor Bogdan Sahlean
1

Sind Sie mit Table Variables entweder fügen Sie einen Primärschlüssel für die Tabelle oder ändern Sie Sie, um Temporary Tables und fügen Sie eine INDEX. Dies führt zu viel mehr Leistung. Als Faustregel gilt, wenn die Tabelle ist nur klein, verwenden TABLE Variables jedoch, wenn die Tabelle erweitert, und viele Daten enthält, dann verwenden Sie entweder eine temp-Tabelle.

InformationsquelleAutor Darren
0

Ich würde geneigt sein, zu versuchen,
```
DECLARE @IdList3 TABLE(Id INT);

INSERT @IdList3
SELECT Id FROM @IDList1 ORDER BY Id
EXCEPT
SELECT Id FROM @IDList2 ORDER BY Id
```
Kein löschen erforderlich.
- Was aber, wenn OP muss zu löschen, wie er/Sie sagte: I need to delete all records from the first list if the Id's already exists in the second list
- stimmt, die OP gibt Ihr ein erfundenes Beispiel betrifft das mit den zwei table-Variablen und insbesondere deletetion. Aber das kann noch nützlich sein für andere Leser.
InformationsquelleAutor Jodrell

-1

Versuchen, diese Alternative syntax:

DELETE deleteAlias
FROM @IdList1 deleteAlias
WHERE EXISTS (
        SELECT NULL
        FROM @IdList2 innerList2Alias
        WHERE innerList2Alias.id=deleteAlias.id
    )

BEARBEITEN.....................

Versuchen Sie es mit #temp-Tabellen mit Indizes statt.

Hier ist ein Allgemeines Beispiel, wo "DepartmentKey" ist die PK und FK.

IF OBJECT_ID('tempdb..#Department') IS NOT NULL
begin
        drop table #Department
end


CREATE TABLE #Department 
( 
    DepartmentKey int , 
    DepartmentName  varchar(12)
)



CREATE INDEX IX_TEMPTABLE_Department_DepartmentKey ON #Department (DepartmentKey)




IF OBJECT_ID('tempdb..#Employee') IS NOT NULL
begin
        drop table #Employee
end


CREATE TABLE #Employee 
( 
    EmployeeKey int , 
    DepartmentKey int ,
    SSN  varchar(11)
)



CREATE INDEX IX_TEMPTABLE_Employee_DepartmentKey ON #Employee (DepartmentKey)


Delete deleteAlias 
from #Department deleteAlias
where exists ( select null from #Employee innerE where innerE.DepartmentKey = deleteAlias.DepartmentKey )





IF OBJECT_ID('tempdb..#Employee') IS NOT NULL
begin
        drop table #Employee
end

IF OBJECT_ID('tempdb..#Department') IS NOT NULL
begin
        drop table #Department
end

Leider ist dies langsam zu. Dasselbe Ergebnis und genau die gleiche Abfrage-plan.
Sind Sie gezwungen, zu verwenden von @variable-Tabellen, oder können Sie versuchen #temp Tabellen?
Wenn Sie können, verwenden Sie #temp-Tabellen, versuchen Sie das Beispiel in meiner Antwort.

InformationsquelleAutor granadaCoder

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.