Tout d'abord, laissez-moi dire que je suis en cours d'exécution sur SQL Server 2005, donc je n'ai pas accès à J'ai une table avec ~ 150K lignes que je me couche quotidiennement à partir d'un fichier texte. Alors que les lignes tombent sur le fichier texte, je dois les supprimer de la base de données et si elles changent ou sont nouvelles, je dois mettre à jour / insérer en conséquence.
Après quelques tests, j'ai trouvé que les performances sage, il est exponentiellement plus rapide de faire une suppression complète, puis une insertion groupée du fichier texte plutôt que de lire la ligne de fichier par ligne effectuant une mise à jour / insertion. Cependant, je suis récemment rencontré des postes discutant de l'imitation de la fonctionnalité Fusionner de SQL Server 2008 à l'aide d'une table Temp et de la sortie de l'instruction Mettre à jour
J'étais intéressé par cela parce que je regarde comment je peux éliminer le temps dans ma méthode de suppression / masse en vrac lorsque la table n'a pas de lignes. Je pense toujours que cette méthode sera la plus rapide de sorte que je cherche la meilleure façon de résoudre le problème de la table vide.
merci Fusionner .
5 Réponses :
Je pense que votre méthode la plus rapide serait de:
Merci pour les conseils, je ne savais pas que Truncate et j'utiliserai probablement, mais j'essaie d'éliminer la période courte entre la suppression et l'insert en vrac lorsque la table est vide. Des idées?
@ RPF3: Essayez le tronquage. Je pense que cela éliminera une grande partie du retard dont vous parlez.
Le tronquage était définitivement plus rapide que le Supprimer mais il faut toujours environ 9 secondes pour l'insert en vrac. On m'a demandé de voir s'il existe un moyen d'éliminer même cette petite quantité de temps d'arrêt, car d'autres processus pourraient frapper la base de données pendant l'exécution.
@ RPF3: Si vous avez suivi les étapes que j'ai données, alors je ne suis au courant de rien d'autre pour accélérer cela. Honnêtement, 9 secondes pour insert en vrac 150k rangées une fois par jour ne me dérait pas déraisonnable.
Oh, je suis tout à fait d'accord avec vous, c'est en fait une amélioration massive de la façon dont elle était faite auparavant en rangée. Cependant, en raison de la nature des données dans le tableau, si une requête a été exécutée et aucune ligne n'a été renvoyée pendant ces 9 secondes, elle pourrait potentiellement être très mauvaise. Il s'agit d'un boîtier de frange mais avec des processus automatisés fonctionnant toute la journée qui risquent de frapper la DB, cela peut arriver. Y a-t-il un moyen de faire l'insertion en vrac dans une table Temp, puis de échanger les deux ou quelque chose?
@ RPF3: Vous pouvez insertion de vrac dans une table Temp et essayer quelque chose comme sp_rename , que je pense nécessiterait une serrure de table qui serait tout aussi nocive. Vous pouvez essayer de créer deux versions de la table et de créer une vue qui alternerait entre les deux alors que l'autre était inséré en vrac. En fin de compte, ma réaction d'intestin est que cela peut être plus sûr et plus facile à coder une logique de manipulation de nouvelle réparation / exception dans ces processus automatisés.
Pour une vitesse brute, je pense que des rangées de 150 000 rangées dans la table, je voudrais simplement laisser tomber la table, la recréer à partir de zéro (sans index) puis la charge en vrac à nouveau. Une fois la charge en vrac terminée, créez les index.
Ceci suppose bien sûr qu'avec une période de temps lorsque la table est vide / n'existe pas est acceptable que cela semble être le cas.
est le problème que la solution de Joe n'est pas assez rapide ou que vous ne pouvez avoir aucune activité contre la table cible pendant que votre processus fonctionne? Si vous avez juste besoin d'empêcher les utilisateurs d'exécuter des requêtes sur votre tableau cible, vous devez contenir votre processus dans un bloc de transaction. De cette façon, lorsque votre table tronquée exécute, il créera un verrou de table qui sera maintenu pendant la durée de la transaction, comme:
begin tran; truncate table stage_table bulk insert stage_table from N'C:\datafile.txt' commit tran;
Je pensais à faire cela, mais si vous n'avez pas d'autorisations pour accéder au Datafile ou au formatFILE, une erreur est lancée qui ne peut pas être prise par SQL Essayer / Catch et arrêtera la transaction du code, le laissant ouverte.
Une solution alternative qui permettrait de disposer de votre condition de ne pas avoir "temps de baisse" pour la table que vous mettez à jour.
On dirait à l'origine que vous lisiez le fichier et que vous effectuiez une ligne insertion / mise à jour / Supprimer 1 ligne à la fois. Une approche plus performante que cela, cela n'implique pas de dégager la table est la suivante:
1) Chargez le fichier dans une nouvelle table séparée (sans index)
2) Créez ensuite le pk sur elle
3) Exécutez 3 déclarations pour mettre à jour la table d'origine de cette nouvelle table (temporaire):
Supprimer des lignes dans la table principale qui n'existe pas dans la nouvelle table
Mettre à jour les rangées dans la table principale où il y a une ligne correspondante dans la nouvelle table
Insérez des rangées dans la table principale de la nouvelle table où elles n'existent pas déjà
Cela fonctionnera mieux que les opérations de lignes à ligne et devront espérons-le que vos exigences globales
Merci, je vais lancer des tests pour voir si je veux utiliser ceci ou simplement garder l'insert en vrac à l'intérieur d'une transaction verrouillée pendant le temps d'exécution court.
Il existe un moyen de mettre à jour la table avec zéro temps d'arrêt: Gardez les données de deux jours dans la table et supprimez les anciennes lignes après charger les nouveaux!
J'ai souvent utilisé cette technique et j'ai également lutté avec des processus comptabilisés sur
Sélectionnez Performance ne souffrira pas; Entrant une rangée à 150 000 rangées le long de la clé primaire ne doit présenter aucun problème à un serveur de moins de 15 ans.
sp_rename . Les processus de production qui modifient le schéma sont un mal de tête. Ne pas.