Base de données: SQL Server 2005
Problème: Copier des valeurs d'une colonne à une autre colonne de la même table avec un milliard +
rangées. choses essayé 1: la requête de mise à jour remplit le journal de transaction et roule en raison du manque de Space de journal de transaction. Essayé 2 - une procédure sur les lignes suivantes La procédure ci-dessus commence à ralentir comme il procède. Essayé 3 - Création d'un curseur pour la mise à jour. Généralement découragé dans la documentation SQL Server et cette approche met à jour une ligne à la fois qui prend trop de temps. existe une approche qui peut accélérer la copie des valeurs d'une colonne à une autre. Fondamentalement, je cherche un mot-clé ou une logique de magie »qui permettra à la requête de mise à jour de déchirer le milliard de rangées un demi-million à une heure séquentiellement. Toute astuce, les pointeurs seront très appréciés.
7 Réponses :
Vous pouvez essayer d'utiliser quelque chose comme puis répétez cela autant de fois que vous en avez besoin. De cette façon, vous évitez les symptômes de curseurs RBAR (rangée à la ligne) de curseurs et de boucles, et pourtant, vous ne remplissez pas inutilement votre journal de transaction. Bien sûr, entre les courses, vous devez faire des sauvegardes (en particulier votre journal) pour garder sa taille dans des limites raisonnables. Set RowCount et faire des mises à jour par lots:
Rowcount travaillera-t-il sur une commande de mise à jour? - msdn.microsoft.com/en-us/library/ms188774.aspx
Oui, il fonctionne jusqu'à SQL 2008 inclus, ne fonctionnera pas dans SQL VNEXT. Mais vous êtes correct, Mise à jour Top 5000 ... est la syntaxe préférée pour SQL 2005 et UP. Voir MSDN.MicRosoft.com/en-us/Library/ms177523.aspx
Aimez l'acronyme RBAR. Je vais devoir voler ça.
@Marc_s Oui, j'interlavais R2 sous le parapluie de "2008". Ils ne provoqueraient pas les caractéristiques entre les révisions numérotées majeures.
Pourquoi cela ne fonctionnerait-il pas à SQL VNext (2011/2012 / quand) ??? Plus vnext sera une version numérotée majeure ... ce qui signifie, selon votre commentaire ultérieur, que cette "fonctionnalité" ne serait pas obsolète.
@Jess: Voir le lien dans le premier commentaire de Dan: "Utilisation de Set RowCount n'affectera pas les instructions de supprimer, d'insertion et de mise à jour de la version suivante de SQL Server. N'utilisez pas SET RowCount avec Supprimer, insérer et mettre à jour les instructions dans le nouveau travail de développement et planifiez de modifier les applications qui l'utilisent actuellement. Aussi, pour supprimer, insérer et mettre à jour les instructions qui utilisent actuellement RowCount, nous vous recommandons de les réécrire à utiliser la syntaxe supérieure. "
@Bradc - Merci pour la tête, cela semble vraiment étrange qu'ils interrompraient son utilisation.
Je n'ai pas couru ceci pour l'essayer, mais si vous pouvez l'obtenir pour mettre à jour 500K à la fois, je pense que vous vous déplacez dans la bonne direction.
ALTER DATABASE db1 SET RECOVERY SIMPLE GO update test_table set bigid = id GO ALTER DATABASE db1 SET RECOVERY FULL GO
Ne recommanderait pas la sous-requête avec la jointure. Trop de travail inutile sur cette énorme table lorsque l'alternative ( bigid = id ) est si simple.
"Le réglage de l'option Set RowCount est ignoré pour insertion, mise à jour et supprimer des instructions contre des tables distantes et des vues partitionnées locales et à distance." de MSDN. Je ne pense pas que le RowCount fonctionnera avec la commande de mise à jour, de sorte que le SELECT est nécessaire pour effectuer le travail de rangée.
Nous ne traitons pas de tables distantes ni de vues partitionnées, de sorte que l'avertissement ne s'applique pas. Mais oui, update top x est probablement préféré vs set RowCount
Est-ce une chose une fois? Si c'est le cas, faites-le simplement par des gammes:
Assurez-vous cela aussi. Selon ce qui est efficace 'où Bigid n'est pas NULL' ou 'où l'identifiant entre x et y' gagne.
+1 à ceci (et voir ma réponse similaire). Cela tire parti de la PK existante sur ID et, en tant que telle, ne devrait pas ralentir à mesure qu'elle progresse. (Tant que vous vous débarrassez du Bigid est null )
première étape, s'il y en a un, il serait nécessaire de supprimer des index avant l'opération. C'est probablement ce qui cause la vitesse de dégrader avec le temps.
L'autre option, un peu en dehors de la boîte en pensant ... Pouvez-vous exprimer la mise à jour de manière à ce que vous puissiez matérialiser les valeurs de colonne dans une sélection? Si vous pouvez le faire, vous pouvez créer ce qui permet de créer une nouvelle table à l'aide d'une opération de sélectionnée (supposant en 2005 que vous êtes défini sur un modèle de récupération de simple ou en vrac enregistré). Ce serait assez rapide et vous pouvez ensuite laisser tomber l'ancienne table, renommer cette table à l'ancien nom de table et recréer tous les index. P>
select id, CAST(id as bigint) bigid into test_table_temp from test_table drop table test_table exec sp_rename 'test_table_temp', 'test_table'
Utiliser haut dans le Mettre à jour la déclaration a >: UPDATE TOP (@row_limit) dbo.test_table
SET bigid = id
WHERE bigid IS NULL
i deuxième la Mettre à jour la déclaration supérieure (x)
Aussi suggérer, si vous êtes dans une boucle, ajoutez-le dans un délai d'attente ou de commettre entre, pour permettre à d'autres processus un peu de temps pour utiliser la table si nécessaire par rapport à jamais jusqu'à ce que toutes les mises à jour soient terminées
Je vais deviner que vous fermez sur la limite de 2,1 milliards d'un type de type de données sur une clé artificielle pour une colonne. Oui, c'est une douleur. Beaucoup plus facile à résoudre avant le fait que, après que vous ayez touché cette limite et que la production est fermée pendant que vous essayez de le réparer :)
Quoi qu'il en soit, plusieurs des idées ici vont fonctionner. Parlons de la vitesse, de l'efficacité, des index et de la taille du journal, cependant.
Le journal a explosé à l'origine parce qu'il tentait de commettre toutes les lignes 2b à la fois. Les suggestions dans d'autres messages pour "le chunking up" fonctionneront, mais qui peuvent ne pas résoudre totalement le problème du journal. Si la base de données est en mode simple, vous serez Fine (le journal sera réutilisé après chaque lot). Si la base de données est en mode de récupération complète ou en bulk_logged, vous devez exécuter fréquemment des sauvegardes de journal pendant la course de votre fonctionnement afin que SQL puisse réutiliser l'espace journal. Cela pourrait signifier d'augmenter la fréquence des sauvegardes au cours de cette période ou de surveiller simplement l'utilisation du journal lors de l'exécution. tout le La clé (jeu de mots) est mon hypothèse que le champ d'identification d'origine est probablement le champ d'identification d'origine. Clé primaire, ou l'indice en cluster, ou les deux. Dans ce cas, profitons de ce fait et faisons une variante de l'idée de Jess: Ceci devrait être extrêmement rapide, en raison des index existants sur ID. < / p> Le chèque Isnull n'était vraiment pas nécessaire de toute façon, ni mon (-1) sur l'intervalle. Si nous dupliquons quelques lignes entre les appels, ce n'est pas une grosse affaire. index et vitesse
où bigid est null < / Code> Les réponses ralentiront alors que la table est peuplée, car il n'y a probablement aucun index sur le nouveau champ Bigid. Vous pouviez, (bien sûr) juste ajouter un index sur Bigid, mais je ne suis pas convaincu que c'est la bonne réponse.
J'ai lancé un script similaire à la vôtre avec une modification car j'ai des identifiants de -2b à + 2b. J'attends que le journal des transactions ne se développera pas à grande échelle, car je mettant la mise à jour des rangées de 500K Max dans un lot. Le journal des transactions est configuré pour augmenter progressivement par 10g jusqu'à 100g. Le journal vient de frapper max et je vois la requête roulant. J'ai même mis un point de départ et vous engager autour de la déclaration de mise à jour pour vous assurer que la transaction est incrémentielle. Ou cela pourrait-il commencer Tran et commettre la boucle peut-être causer la croissance du journal de transaction?
On dirait que le tran de départ et commettre que j'avais ajouté, causait le journal de transaction remplissant. Enlevé ceux de mon script et la procédure a déchiré les rangées de la nuit nette pendant la nuit sans remplir le journal des transactions :-)
@Adi: Tant que vous avez la bonne syntaxe ( Commencer Tran et commettre trans ), puis mettez ceux sur la boucle intérieure devraient être le Identique que mon script (SQL utilise des transactions implicites par défaut, c'est pourquoi les combats comme ces travaux). Une fois, j'ai mis juste commettez au lieu de commettre trans et fini par des transactions nichées folles qui ont explosé le journal tel que vous avez dit. Il suffit de ne pas mettre la transaction autour de la à l'extérieur de la boucle.
Merci @Bradc Acceptant votre solution comme meilleure réponse car elle a la meilleure performance constante et utilise bien l'index existant. Il suffit de modifier un peu le script, car mon champ a des valeurs de -2b à + 2b et ajuste également les valeurs de compteur pour le dernier lot pour éviter le débordement.
Pourquoi voulez-vous deux colonnes dans la table avec la même valeur? Peut-être qu'il y a une autre approche pour résoudre votre problème
Je suppose qu'il est proche de la limite de 2.1billion pour le type de données INT sur une clé artificielle ascendante, et il essaie de le changer à un Bigint. Il a probablement déjà découvert que vous ne pouvez pas changer cela facilement :)
Yeah Mike, la supposition de Brad est juste sur. @Bradc lit les esprits :-)