Créer une clé primaire sur une table temporaire - quand?

j'ai une procédure stockée qui est de travailler avec une grande quantité de données. Ces données sont insérées dans une table de température. Le flux global des événements est quelque chose comme

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?

ma question Est de savoir quand est le meilleur moment pour créer une clé primaire sur ma table #TempTable? j'ai théorisé que je devrais créer la contrainte/index clé primaire après avoir inséré toutes les données parce que l'index doit être réorganisé comme l'information clé primaire est en cours de création. Mais j'ai réalisé que mon soulignant hypothèse pourrait être mal...

dans le cas où c'est pertinent, les types de données que j'ai utilisés sont réels. Dans le #TempTable tableau, Col1 et Col4 va rendre ma clé primaire.

mise à Jour: dans mon cas, je copie la clé primaire des tables source. Je sais que les champs qui composeront ma clé primaire seront toujours uniques. Je n'ai aucune inquiétude au sujet d'un alter table échoué si j'ajoute la clé primaire à la fin.

Si, cela mis à part, ma question se pose toujours comme qui est plus rapide en supposant que les deux réussiraient<!--8?

P.S. Je suis désolé si c'est un duplicata. C'est assez basique que ça puisse l'être, mais je n'ai rien trouvé de tel.

19
demandé sur Malachi 2009-06-23 01:09:58

9 réponses

cela dépend beaucoup.

si vous faites l'index de la clé primaire groupé après la charge, la table entière sera réécrite car l'index groupé n'est pas vraiment un index, c'est l'ordre logique des données. Votre plan d'exécution sur les inserts, va dépendre de l'index en place lorsque le régime est déterminé, et si l'index cluster est en place, il va trier avant de les insérer. Vous verrez généralement présent dans le plan d'exécution.

Si vous prendre la clé primaire d'une simple contrainte, il sera régulièrement (index non cluster et le tableau sera simplement peuplé, dans l'ordre que l'optimiseur détermine et l'index mis à jour.

je pense que la performance globale la plus rapide (de ce processus pour charger la table temp) est habituellement d'écrire les données comme un tas et puis appliquer l'indice (non-groupé).

cependant, comme d'autres l'ont fait remarquer, la création de l'indice pourrait échouer. De plus, la table des températures n'existe pas dans isolement. Il y a probablement un meilleur indice pour lire les données de celui-ci pour l'étape suivante. Cet index devra être en place ou créé.

14
répondu Cade Roux 2009-06-22 22:20:42

si le modèle de récupération de votre base de données est défini à simple ou en vrac, sélectionnez ... DANS. .. UNION tous peut être la solution la plus rapide. SÉLECTIONNER. . Il s'agit d'une opération en vrac et les opérations en vrac sont enregistrées au minimum.

par exemple:

-- 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)

sinon, Cade Roux avait de bons conseils sur: avant ou après.

6
répondu Peter Radocchia 2009-06-23 01:42:30

vous pouvez aussi bien créer la clé primaire avant les inserts - si la clé primaire est sur une colonne d'identité alors les inserts seront faits séquentiellement de toute façon et il n'y aura aucune différence.

3
répondu Justin 2009-06-22 21:13:11

encore plus important que les considérations de performance, si vous n'êtes pas absolument sûr à 100% que vous aurez des valeurs uniques insérées dans la table, créez d'abord la clé primaire. Sinon, la clé primaire ne sera pas créée.

ceci vous empêche d'insérer des doublons/données erronées.

2
répondu Jeff Meatball Yang 2009-06-22 22:09:10

si vous ajoutez la touche primaire lors de la création de la table, le premier insert sera libre (aucune vérification requise.) Le second insert doit juste voir s'il est différent du premier. La troisième insertion doit vérifier les deux lignes, et ainsi de suite. Les vérifications seront des recherches d'index, parce qu'il y a une contrainte unique en place.

si vous ajoutez la clé primaire après tous les inserts, chaque ligne doit être comparée à chaque autre ligne. Donc à mon avis, ajouter une clé primaire tôt est moins cher.

mais peut-être que Sql Server a une façon très intelligente de vérifier l'unicité. Donc, si vous voulez être sûr, de le mesurer!

1
répondu Andomar 2009-06-22 21:49:08

je me demandais si je pouvais améliorer une procédure stockée très "chère" impliquant un tas de vérifications à chaque insertion à travers les tableaux et je suis tombé sur cette réponse. Dans le Sproc, plusieurs tables de température sont ouvertes et se réfèrent les unes aux autres. J'ai ajouté la clé primaire à L'instruction CREATE TABLE (même si mes selects utilisent des instructions WHERE NOT EXISTS pour insérer des données et assurer l'unicité) et mon temps d'exécution a été réduit sévèrement. Je recommande fortement d'utiliser les clés primaires. Toujours au moins essayer même quand tu penses que tu n'en as pas besoin.

1
répondu 2012-08-11 20:30:33

je ne pense pas que cela fait une différence significative dans votre cas:

  • soit vous payer la pénalité, un peu à la fois, chaque seule insert
  • ou vous aurez à payer une plus grande peine après toutes les insertions sont faites, mais seulement une fois

quand vous le créez avant le début des inserts, vous peut-être attraper des violations de PK lorsque les données sont insérées, si la valeur de PK n'est pas créée par le système.

mais autre que cela - pas de grande différence, vraiment.

Marc

0
répondu marc_s 2009-06-22 21:18:16

Je n'avais pas l'intention de répondre à cette question, puisque je ne suis pas sûr à 100% de ce que je sais. Mais comme il ne semble pas que vous obtenez beaucoup de réponse ...

Ma compréhension est un PK est un index unique et lorsque vous insérez chaque enregistrement, votre index est mis à jour et optimisé. Si. .. si vous ajoutez les données en premier, puis créez l'index, l'index n'est optimisé qu'une fois.

donc, si vous êtes sûr que vos données sont propres (sans données PK dupliquées) alors je dirais insert, puis ajouter le PK.

mais si vos données peuvent avoir des données PK dupliquées, je dirais créer le PK d'abord, donc il va exploser dès que possible.

0
répondu John MacIntyre 2009-06-22 21:59:10

quand vous ajoutez PK sur la création de table - la vérification d'insertion est O(Tn) (où Tn est "n-ième nombre triangulaire", qui est 1 + 2 + 3 ... + n) parce que quand vous insérez x-th ligne, il est vérifié par rapport à précédemment inséré" x - 1 " lignes

quand vous ajoutez PK après insérant toutes les valeurs - le vérificateur est O(n^2) parce que quand vous insérez X-E ligne, il est coché contre tous n les lignes existantes.

le premier est évidemment plus rapide puisque O(Tn) est inférieur à O(n^2)

P.S. exemple: si vous insérez 5 lignes, c'est 1 + 2 + 3 + 4 + 5 = 15 opérations vs 5^2 = 25 activités

0
répondu Alex 2018-05-14 11:31:15