wake-up-neo.com

Erstellen eines Primärschlüssels auf einer temporären Tabelle - Wann?

Ich habe eine gespeicherte Prozedur, die mit einer großen Datenmenge arbeitet. Ich habe diese Daten in eine temporäre Tabelle eingefügt. Der Gesamtfluss der Ereignisse ist so ähnlich

CREATE #TempTable (
    Col1    NUMERIC(18,0) NOT NULL,    --This will not be an identity column.
    ,Col2   INT NOT NULL,
    ,Col3   BIGINT,

    ,Col4   VARCHAR(25) NOT NULL,
    --Etc...

    --
    --Create primary key here?
)


INSERT INTO #TempTable
SELECT ...
FROM MyTable
WHERE ...

INSERT INTO #TempTable
SELECT ...
FROM MyTable2
WHERE ...

--
-- ...or create primary key here?

Meine Frage ist, wann der beste Zeitpunkt ist, um einen Primärschlüssel in meiner Tabelle #TempTable zu erstellen. Ich habe theoretisiert, dass ich den Primärschlüssel-Integritäts-/Index erstellen soll, nachdem ich alle Daten eingefügt habe, da der Index neu erstellt werden muss, während die Primärschlüsselinformationen erstellt werden. Aber mir wurde klar, dass meine unterstrichene Annahme falsch sein könnte ...

Wenn es relevant ist, sind die verwendeten Datentypen echt. In der #TempTable-Tabelle bilden Col1 und Col4 meinen Primärschlüssel. 

Update: In meinem Fall dupliziere ich den Primärschlüssel der Quellentabellen. Ich weiß, dass die Felder, aus denen mein Primärschlüssel besteht, immer eindeutig sind. Ich habe keine Bedenken wegen einer fehlgeschlagenen Änderungstabelle, wenn ich am Ende den Primärschlüssel hinzufüge. 

Abgesehen davon, meine Frage steht immer noch dahin, was schneller ist, vorausgesetzt, beide wären erfolgreich

P.S. Es tut mir leid, wenn dies ein Duplikat ist. Es ist grundlegend genug, dass es sein könnte, aber ich konnte so etwas nicht finden. 

20
Frank V

Dieses hängt sehr.

Wenn Sie den Primärschlüsselindex nach dem Laden zu einem Cluster zusammenfassen, wird die gesamte Tabelle neu geschrieben, da der Clusterindex kein Index ist, sondern die logische Reihenfolge der Daten. Ihr Ausführungsplan für die Einfügungen hängt von den bei der Festlegung des Plans vorhandenen Indizes ab. Wenn der gruppierte Index vorhanden ist, wird er vor dem Einfügen sortiert. Sie werden dies normalerweise im Ausführungsplan sehen.

Wenn Sie den Primärschlüssel zu einer einfachen Einschränkung machen, handelt es sich um einen regulären (nicht gruppierten) Index. Die Tabelle wird einfach in der Reihenfolge aufgefüllt, die das Optimierungsprogramm festlegt, und der Index wird aktualisiert.

Ich denke, die allgemein schnellste Leistung (dieses Prozesses zum Laden der temporären Tabelle) besteht normalerweise darin, die Daten als Heap zu schreiben und dann den (nicht gruppierten) Index anzuwenden.

Wie andere jedoch festgestellt haben, könnte die Erstellung des Index fehlschlagen. Die temporäre Tabelle ist auch nicht isoliert vorhanden. Vermutlich gibt es einen besten Index für das Lesen der Daten für den nächsten Schritt. Dieser Index muss entweder vorhanden sein oder erstellt werden. This Hier müssen Sie Geschwindigkeit ausgleichen, um die Zuverlässigkeit zu gewährleisten (wenden Sie zuerst den PK und alle anderen Einschränkungen an) und beschleunigen Sie später (haben Sie mindestens den gruppierten Index, falls Sie eine haben werden.) .

15
Cade Roux

Wenn das Wiederherstellungsmodell Ihrer Datenbank auf Einfach oder Massenprotokollierung eingestellt ist, ist SELECT ... INTO ... UNION ALL möglicherweise die schnellste Lösung. SELECT .. INTO ist ein Massenvorgang und Massenvorgänge werden minimal protokolliert. 

z.B:

-- first, create the table
SELECT ...
INTO #TempTable
FROM MyTable
WHERE ...
UNION ALL
SELECT ...
FROM MyTable2
WHERE ...

-- now, add a non-clustered primary key:
-- this will *not* recreate the table in the background
-- it will only create a separate index
-- the table will remain stored as a heap
ALTER TABLE #TempTable ADD PRIMARY KEY NONCLUSTERED (NonNullableKeyField)

-- alternatively:
-- this *will* recreate the table in the background
-- and reorder the rows according to the primary key
-- CLUSTERED key Word is optional, primary keys are clustered by default
ALTER TABLE #TempTable ADD PRIMARY KEY CLUSTERED (NonNullableKeyField) 

Ansonsten hatte Cade Roux gute Ratschläge vor oder nach.

6
Peter Radocchia

Sie können den Primärschlüssel auch vor den Einfügungen erstellen. Wenn sich der Primärschlüssel in einer Identitätsspalte befindet, werden die Einfügungen ohnehin nacheinander ausgeführt, und es gibt keinen Unterschied.

3
Justin

Noch wichtiger als Leistungserwägungen: Wenn Sie nicht ABSOLUT sind und 100% sicher sind, dass eindeutige Werte in die Tabelle eingefügt werden, erstellen Sie zuerst den Primärschlüssel. Andernfalls kann der Primärschlüssel nicht erstellt werden. 

Dies verhindert, dass Sie doppelte/falsche Daten einfügen.

2

Ich habe mich gefragt, ob ich eine sehr "teure" gespeicherte Prozedur verbessern könnte, die eine Reihe von Überprüfungen an jedem Einsatz über Tabellen hinweg beinhaltet, und auf diese Antwort gestoßen ist. Im Sproc werden mehrere temporäre Tabellen geöffnet und referenzieren sich. Ich habe der CREATE TABLE-Anweisung den Primärschlüssel hinzugefügt (obwohl meine select-Anweisungen WHERE NOT EXISTS-Anweisungen verwenden, um Daten einzufügen und die Eindeutigkeit sicherzustellen) und meine Ausführungszeit SEVERELY verkürzt wurde. Ich empfehle dringend die Verwendung der Primärschlüssel. Probieren Sie es immer mindestens aus, auch wenn Sie denken, Sie brauchen es nicht. 

1
user1076406

Wenn Sie beim Erstellen der Tabelle den Primärschlüssel hinzufügen, ist die erste Einfügung frei (keine Prüfung erforderlich). Die zweite Einfügung muss nur feststellen, ob sie sich von der ersten unterscheidet. Die dritte Einfügung muss zwei Zeilen überprüfen und so weiter. Bei den Prüfungen handelt es sich um Index-Lookups, da eine eindeutige Einschränkung vorhanden ist.

Wenn Sie den Primärschlüssel nach allen Einfügungen hinzufügen, muss jede Zeile mit jeder anderen Zeile verglichen werden. Meine Vermutung ist also, dass das frühere Hinzufügen eines Primärschlüssels billiger ist. 

Aber vielleicht hat Sql Server eine wirklich intelligente Möglichkeit, die Eindeutigkeit zu überprüfen. Wenn Sie also sicher sein wollen, messen Sie es!

1
Andomar

Ich glaube nicht, dass es in Ihrem Fall einen signifikanten Unterschied macht:

  • entweder Sie zahlen die Strafe für jede einzelne Einlage ein wenig 
  • oder Sie zahlen eine höhere Strafe, nachdem alle Einsätze ausgeführt wurden, jedoch nur einmal 

Wenn Sie es vor dem Einfügen der Inserts erstellen, könnten Sie möglicherweise PK-Verletzungen beim Einfügen der Daten feststellen, wenn der PK-Wert nicht vom System erstellt wird. 

Ansonsten aber kein großer Unterschied.

Marc

0
marc_s

Wenn Sie PK bei der Tabellenerstellung hinzufügen, lautet die Einfügeprüfung O(Tn) (wobei Tn "n-te dreieckige Zahl" ist, dh 1 + 2 + 3 ... + n), da beim Einfügen der x-ten Zeile die zuvor eingefügten "x - 1" -Zeilen geprüft werden

Wenn Sie PK after einfügen - alle Werte einfügen - ist die Überprüfung O(n^2), da beim Einfügen der x-ten Zeile alle vorhandenen n-Zeilen geprüft werden.

Der erste ist offensichtlich schneller, da O(Tn) weniger als O(n^2) ist.

P.S. Beispiel: Wenn Sie 5 Zeilen einfügen, handelt es sich um 1 + 2 + 3 + 4 + 5 = 15 Operationen vs. 5^2 = 25 Operationen

0
Alex

Ich hatte nicht vor, darauf zu antworten, da ich nicht zu 100% auf mein Wissen darüber bin. Aber da es nicht so aussieht, als würden Sie viel Antwort bekommen ...

Mein Verständnis ist, dass ein PK ein eindeutiger Index ist. Wenn Sie jeden Datensatz einfügen, wird Ihr Index aktualisiert und optimiert. Wenn Sie also zuerst die Daten hinzufügen und dann den Index erstellen, wird der Index nur einmal optimiert.

Wenn Sie also sicher sind, dass Ihre Daten sauber sind (ohne doppelte PK-Daten), würde ich sagen, dass Sie Einfügen und dann die PK hinzufügen.

Wenn Ihre Daten jedoch doppelte PK-Daten haben, würde ich sagen, dass Sie zuerst die PK erstellen sollten, damit sie so schnell wie möglich aus dem Spiel geht.

0
John MacIntyre