Insertion lente pour une table avec de nombreux index
j'essaie d'insérer des millions d'enregistrements dans une table qui a plus de 20 d'index.
dans la dernière exécution, il a fallu plus de 4 heures pour 100.000 lignes, et la requête a été annulée après 3½ jours...
avez-vous des suggestions sur la façon d'accélérer les choses.
(je soupçonne les nombreux indices d'en être la cause. Si vous le pensez aussi, comment puis-je supprimer automatiquement les index avant l'opération, puis créer les mêmes index après encore?)
informations Supplémentaires:
- L'espace utilisé par l'index est d'environ 4 fois à l'espace utilisé par les données
- les inserts sont emballés dans une transaction par 100.000 lignes.
mise à Jour sur l'état d'avancement:
la réponse acceptée m'a aidé à la rendre beaucoup plus rapide.
4 réponses
Vous pouvez désactiver et activer l'index. Notez que les désactiver peut avoir des effets secondaires indésirables (comme avoir des clés primaires dupliquées ou des indices uniques, etc.).) qui ne sera trouvé qu'en réactivant les index.
--Disable Index
ALTER INDEX [IXYourIndex] ON YourTable DISABLE
GO
--Enable Index
ALTER INDEX [IXYourIndex] ON YourTable REBUILD
GO
cela ressemble à une opération d'entrepôt de données. Il serait normal de laisser tomber les index avant l'insertion et de les reconstruire ensuite.
quand vous reconstruisez les indices, construisez l'indice groupé en premier, et inversement le laisser tomber en dernier. Ils devraient tous avoir fillfactor 100%.
le Code devrait être quelque chose comme ceci
if object_id('Index') is not null drop table IndexList
select name into Index from dbo.sysindexes where id = object_id('Fact')
if exists (select name from Index where name = 'id1') drop index Fact.id1
if exists (select name from Index where name = 'id2') drop index Fact.id2
if exists (select name from Index where name = 'id3') drop index Fact.id3
.
.
BIG INSERT
RECREATE THE INDEXES
tel que noté par une autre réponse désactiver les index sera un très bon début.
4 heures pour 100.000 rangs [...] Les inserts sont emballés dans une transaction pour 100.000 lignes.
vous devriez chercher à réduire le nombre, le serveur doit maintenir une quantité énorme de l'État tandis que dans une transaction (de sorte qu'il peut être retranché), cela (avec les index) signifie que l'ajout de données est un travail très dur.
pourquoi ne pas envelopper chaque insert dans son une transaction personnelle?
regardez aussi la nature du SQL que vous utilisez, est-ce que vous ajoutez une ligne par statement (et réseau roundtrip), ou en ajoutez beaucoup?
les indices de désactivation et de réactivation sont fréquemment suggérés dans ces cas. J'ai des doutes sur cette approche, parce que:
(1) L'utilisateur DB de l'application a besoin de privilèges de modification de schéma, qu'il ne devrait normalement pas posséder. (2) l'approche insert et/ou le schéma d'index choisi pourraient être moins qu'optimaux en premier lieu, sinon la reconstruction des arbres d'index complets ne devrait pas être plus rapide que l'insertion d'un lot décent (par exemple le client émettant un insert déclaration à la fois, provoquant des milliers de roundtrips de serveur; ou un mauvais choix sur l'index groupé, conduisant à des divisions constantes de noeud d'index).
C'est pourquoi mes suggestions semblent un peu différentes:
- choisissez l'index groupé de la table cible sagement, de sorte que les inserts ne conduiront pas à des divisions de noeuds d'index groupés. Habituellement, une colonne d'identité est un bon choix
- laisser le client insérer dans un tas temporaire une table (tables heap n'ont pas d'index cluster); puis, la question d'un gros "insérez-en-sélectionnez" déclaration de pousser tous que la tenue de la table de données dans la table cible
- Apply SqlBulkCopy
- diminuer la journalisation des transactions en choisissant le modèle de récupération en vrac
vous pouvez trouver plus d'informations détaillées dans cet article.