Pourquoi SQL server insère-elle si lente?
J'essaie d'insérer des lignes de données en mémoire dans une table SQL Server Express 2005. Il est en cours d'exécution ce qui me semble très lentement - environ 5 secondes par 1000 lignes insérées. Je suis juste en utilisant une base "INSERT INTO commande". La lenteur ne dépend pas de la table de données - il est encore lent avec un tableau avec une colonne int et pas d'index. Il n'en est rien à faire avec mon logiciel c'est juste que la lenteur de l'exécution de SQL dans une boucle à partir de Management Studio. Il n'y a rien d'autre accès à la base de données en même temps. Sur un Xeon 3Ghz (vieux je sais), cela prendra environ 10 secondes à s'exécuter:
declare @i int
set @i = 0
set nocount on
while @i < 2000
begin
insert into testdb(testcolumn)
values (1)
set @i = @i + 1
end
Est-il une meilleure façon d'insertion en bloc de données en mémoire que l'on boucle sur les INSÉRER? Ou certains de configuration je dois changer dans SQL Server?
OriginalL'auteur Peter | 2009-11-16
Vous devez vous connecter pour publier un commentaire.
Vous effectuez chaque insérer à l'intérieur de sa propre transaction.
Début et de commettre de transaction est très cher en
SQL Server
.Placez le tout dans une seule transaction de bloc:
Pour générer des données d'exemple, vous pouvez utiliser un récursif
CTE
:qui sera plus rapide.
Je n'ai pas réaliser à quel point beaucoup de différence ce fait jusqu'à ce que je l'ai essayé moi - même- 200 000 lignes ajoutées à l'aide de l'affiche du code enveloppé dans une transaction de 1,3 secondes. Sans l'opération, il a fallu 47 secondes.
Alsmot chaque article dans mon blog tagged
sqlserver
contient un script pour remplir les tables de test. J'ai l'habitude d'utiliser reproductibleRAND
approche pour générer les données de test, de sorte que les scripts utilisent cette approche et sont très rapides.génial, merci
Très bon. J'aimerais utiliser le seul insérer approche n'aurait jamais pensé sur le bouclage d'être mieux à l'intérieur d'une transaction.
OriginalL'auteur Quassnoi
1) connectez-vous à Chasse sur validation. Chaque transaction doit veiller à ce que le journal est vidée sur le disque avant de le commettre des rendements. Chaque instruction INSERT est une transaction implicite. En vrac commettre:
2) la Lenteur du disque. De vérifier la Avg. Disque s/Transfert compteur de performance pour vos données et vos disques de journal.
3) De nombreux indices (peu probable sur une table de test). Chaque indice est presque aussi cher qu'un "tableau" pour les insertions.
4) les Déclencheurs (encore une fois, peu probable)
En fin de compte, de mesure. Suivre les directives d'un livre blanc, comme Dépannage des Problèmes de Performances dans SQL Server 2005 si vous ne savez pas par où commencer.
commit; begin transaction;
dans votre boucle. Votre exemple de code ne semble pas être montrant une masse de commettre, mais plutôt explicite s'engager sur la portée de l'insertion. Ou ai-je raté quelque chose?Il engage tous les 1000 inserts (@i modulo 1000 = 0 -> tous les 1000 inserts de faire un commit). Je voulais montrer que n'est pas nécessairement requis pour s'engager tout en une seule opération, il pourrait y avoir des millions d'inserts. L'important, c'est de remplir la page du journal avant de rincer (commit).
Bon point sur les lots s'engage
OriginalL'auteur Remus Rusanu
Vous avez beaucoup de techniques et d'outils pour obtenir plus de performance de ce type de charge de travail.
OriginalL'auteur Chad
En plus d'indices, si vous êtes réel scénario est que par votre exemple, vous pourriez faire un jeu basé sur l'approche d'insérer des enregistrements de 2000 comme ceci:
1
à unROW_NUMBER
, bien 🙂C'est en s'appuyant sur spt_values avoir plus de 2000 valeurs. Vrai pour SQL2005/2008, mais je n'aime pas cette méthode. Mon spt_values a 2193, à peine assez.
Et comme vous obtenir un nombre plus élevé, vous pouvez avoir des lacunes, donc convenu que vous devez utiliser la fonction row_number. Un plus fiables de la table sys.les colonnes si vous avez une assez importante schéma.
J'allais juste sur l'ouverture d'un poste, qui vient d'ajouter 1. @CodeByMoonlight - j'étais juste à démontrer l'exact scénario publié dans la question, pour laquelle il n'ont assez de valeurs. Si plus sont nécessaires, vous devriez juste de la croix se joindre à lui-même pour obtenir plus. Le point sur lequel je suis en train de faire ici, c'est d'essayer un jeu basé sur l'approche!! J'ai choisi une approche basée sur n'importe quel jour de la semaine dans la mesure du possible.
OriginalL'auteur AdaTheDev
Insérez la vitesse est motivée par les éléments suivants:
Dans le cas où il est d'aucun intérêt, je passe par ce point en détail dans mon livre (Ultra-Rapide ASP.NET), notamment des points de référence et un exemple de code.
OriginalL'auteur RickNZ
Ayant un index cluster (généralement de clé primaire) augmente de fait insérer vitesse, afin de vérifier que vous disposez de l'un de ceux-ci. Et en cours d'exécution 1000 transactions par rapport à une table n'est pas la façon la plus rapide si vous pouvez avoir l'ensemble des données à la fois et l'insérer dans la table (Ce qui peut être accompli en utilisant la table des paramètres dans sql server 2008 ou de paramètres xml en 2005).
OriginalL'auteur marr75
Je google à "Réglage de SQL Server"... Il y a beaucoup de livres écrits sur le sujet. C'est une chose très difficile à résoudre car il ya BEAUCOUP de choses qui affectent la vitesse, de la syntaxe de la requête, de la mémoire vive allouée au serveur, les proportions de RAM allouée (pour la partie de SQL Server que vous allouer de la RAM), RAID configuration de la matrice, et de NOMBREUX autres facteurs. Vous pouvez avoir un serveur de base de données optimisée pour insérer/mises à jour (OLTP) ou pour l'interrogation (entrepôt de données type de choses). En d'autres termes, ne vous attendez pas à un simple, réponse simple à cela, même pensé que ton problème semble simple.
C'est pourquoi vous avez de la base de données des administrateurs de serveur.
Ou vous pouvez simplement pas la sueur du côté serveur, le les problèmes et optimiser votre client-code autant que possible, si le temps n'est pas très important pour vous.
Je veux le regarder dans les instructions préparées et les transactions comme une façon de commencer à optimiser. Ensuite, regardez l'indexation (si c'est un ensemble d'insertions qui n'arrive pas très souvent, je voudrais envisager de l'abandonner indices, de procéder à l'importation et de la création d'indices de nouveau).
OriginalL'auteur Gabriel Magana