Question Comment accélérer l'ajout d'une colonne à une grande table dans Sql Server


Je veux ajouter une colonne à une table SQL Server avec environ 10 millions de lignes. Je pense que cette requête finalement finissez d'ajouter la colonne que je veux:

alter table T
add mycol bit not null default 0

mais cela dure déjà depuis plusieurs heures. Existe-t-il un raccourci pour insérer une colonne "not null default 0" dans une grande table? Ou est-ce intrinsèquement très lent?

C'est SQL Server 2000. Plus tard, je dois faire quelque chose de similaire sur SQL Server 2008.


7
2017-07-06 23:17


origine




Réponses:


En fonction de la taille de votre ligne, de la taille de la table, des index, etc., j'ai vu SQL Server 2000 perdre du temps pendant plusieurs heures (4 à 5 fois) avant de terminer.

La pire chose que vous puissiez faire en ce moment est de "paniquer" et de tuer le plus durement possible. Laissez-le s'épancher.

À l'avenir, vous voudrez peut-être essayer de faire ce que Farseeker a mentionné, créer une seconde structure (vide) et copier vos enregistrements de cette façon.

  • Plus la ligne de la table est longue, plus cela prendra de temps.
  • Plus vous avez d'index sur cette table, plus cela prendra de temps.
  • Si vous ajoutez une valeur par défaut (ce que vous avez fait), cela prendra plus de temps.
  • Si vous utilisez beaucoup le serveur, cela prendra plus de temps.
  • Si vous ne verrouillez pas cette base de données ou ne la mettez pas en mode mono-utilisateur, cela prendra plus de temps.

Quand je dois faire des choses laides comme ça, j'essaie de le faire la nuit ... comme 2 heures du matin quand personne ne se trouve dessus (et la maintenance ne fonctionne PAS sur le serveur).

Bonne chance! :-)


4
2017-07-07 01:25





Hmm, 10 millions de lignes, c'est assez peu, mais ce n'est pas en dehors de MSSQL et ça semble très lent.

Nous avions une table avec une taille de rangée énorme (mal conçue) et plus de 10 millions de rangées. Lorsque nous avons dû modifier la structure, c'était définitif. très lentement, donc ce que nous avons fait était (de garder la table en ligne, et c’est brutal de mémoire car c’était il y a longtemps):

  • Création d'une nouvelle table avec le suffixe "C" (pour la conversion) et une nouvelle structure (identique à l'ancienne, mais avec une nouvelle colonne / index / etc)
  • SELECT * INTO tableC FROM table
  • sp_rename 'table' 'tableOld'
  • sp_rename 'tableC' 'table'

De cette façon, le temps nécessaire à la conversion importe peu, car les anciennes données sont en ligne. Cela pourrait poser des problèmes d'écriture de lignes dans la table pendant la conversion (ce n'était pas un problème pour nous, car les données n'étaient écrites qu'une fois par jour, mais interrogées des milliers de fois par heure). .


6
2017-07-06 23:45





Vous pouvez essayer d'exécuter chaque étape de l'opération dans un lot séparé, par exemple.

alter table T add mycol bit null
go
update T set mycol = 0
go
alter table T alter column mycol bit not null
go
alter table T add default 0 for mycol
go

Les avantages sont:

  • Vous obtenez un meilleur retour d’information sur l’avancement de l’opération, car il s’agit maintenant de 4 lots distincts, chacun prenant environ 1/4 du temps.
  • Il réduit le risque d'erreur de dépassement de délai lors de son exécution à partir d'un code côté client.
  • Je trouve que cela améliore parfois les performances.

Vous pouvez également essayer de supprimer tous les index non clusterisés de la table avant de procéder à la modification, puis de les restaurer. L'ajout d'une colonne peut impliquer des fractionnements de page à grande échelle ou d'autres réaménagements de bas niveau, et vous pouvez vous en passer sans la surcharge de la mise à jour des index non clusterisés.


5
2017-07-30 12:02



Cette approche est fortement recommandée. Si nécessaire, vous pouvez même diviser cette seconde étape en morceaux afin de ne mettre à jour qu'un sous-ensemble de lignes à la fois, en fonction des plages de clé primaire ou en utilisant SET ROWCOUNT (for sql 2000) ou UPDATE TOP 1000 (pour SQL 2005). / 2008). - BradC


Cela prendra un certain temps. C'est parce que vous ajoutez la valeur par défaut. Cela oblige SQL Server à mettre à jour toutes les lignes d'une seule transaction. Assurez-vous que personne n'utilise la table car cela pourrait bloquer votre processus.


3
2017-07-07 00:33





J'ai fait des choses similaires dans un tableau avec au moins 65 millions de lignes et cela n'a pas pris autant de temps. Avez-vous assez de mémoire et une performance suffisante dans le système de disque?

Si vous souhaitez accélérer le processus, vous pouvez supprimer tous les index sauf les contraintes d'index en cluster et de clé étrangère avant de modifier la table, mais vous devez le faire lorsque le système n'est pas utilisé, sinon vous risquez de vous retrouver avec des données incohérentes. Mais au final, vous devrez appliquer les clés étrangères et les index avant d’avoir terminé, mais vous allégerez les difficultés du journal des transactions, du moins si vous utilisez un modèle de récupération simple. Et dans SQL Server 2008, vous pouvez créer les index avec ONLINE = on et SORT_IN_TEMPDB = on

Håkan Winther


1
2017-07-07 06:52





Vous n'allez pas vraiment raccourcir quelque chose comme ceci - quoi que vous fassiez, SQL Server devra effectuer un traitement sur toutes les lignes de la table.

Vous pouvez vous assurer qu'il fonctionne aussi vite que possible en vous assurant que vos fichiers de données et vos journaux sont sur des lecteurs distincts et en suivant les autres recommandations habituelles.


0
2017-07-06 23:45





Les heures pour 10 m de lignes sont beaucoup trop longues. Vérifiez que rien ne tient les verrous ouverts sur la table.


0
2017-07-07 00:39





Lors d’une formation, j’ai eu une conversation avec deux DBA du DoD. Ils gèrent des bases de données MySQL de 100 To et plus. Les modifications de table sont effectuées avec dump et load, mais cela nécessite évidemment un certain temps d'indisponibilité. Ils ont également mentionné qu'ils n'aimaient pas faire cela avec des bases de données de plus de 10 To en raison du temps nécessaire.

Les données sont vidées, elles ne spécifient pas quoi mais je suppose que les fichiers SQL. Les tables sont ensuite tronquées et le schéma modifié selon les besoins. Les données sont ensuite rechargées.


0
2017-07-07 03:49





Avez-vous eu plusieurs index pour votre table et même un index en cluster sur votre table T?

J'ai aussi eu du mal à ajouter une nouvelle colonne (c'est une colonne d'identité). La table comportait 9,3 millions de lignes et un index non clusterisé sur la clé primaire.

Pour une raison quelconque si nous déposer l'index pour la table T, puis ajoutez la colonne, puis rajoutez l’index de la table T. C’était 60 fois plus rapide sur le serveur SQL Server 2008 standard.

Je ne comprends pas pourquoi cela a tellement accéléré, j'espère que quelqu'un pourra me répondre.


0
2018-05-13 23:04



La raison pour laquelle le processus accélère 60 fois lorsque l'index est supprimé est due à: Si vous avez un index, SQL Server doit organiser les enregistrements de la table dans un ordre particulier. Si vous avez un index sur une colonne qui a: A B C F et que vous décidez d’ajouter "D", cet enregistrement devra être inséré entre "C" et "F". Le serveur doit d'abord trouver la position, puis insérer l'enregistrement. D'où l'opération fastidieuse lors de l'insertion de données sur des tables indexées.
@Terminator Ceci n’est correct que si vous parlez d’un CLUSTERED indice. Un index normal n'affecte pas l'ordre des tables - il stocke simplement une valeur de clé et un index dans la table de la base de données (généralement dans un arbre ou une structure similaire). L'indice de raison ralentit le ALTER TABLE Cette commande est due au fait que chaque index doit être mis à jour (pour 10 millions de lignes par index), ce qui représente une quantité substantielle d’E / S de disque supplémentaires et d’activités de calcul. - voretaq7