Les meilleures pratiques pour l'insertion, la mise à jour de grande quantité de données dans SQL Server 2008

Je suis en train de construire un système pour la mise à jour de grandes quantités de données à travers différents flux CSV. Normalement, j'aurais juste effectue une boucle pour chaque ligne dans l'alimentation, de faire une requête select pour vérifier si l'élément existe déjà et insérer/mettre à jour un élément en fonction de si il existe ou pas.

Je sens que cette méthode n'est pas très évolutif et peut marteau le serveur sur de plus grands flux. Ma solution est de faire une boucle par les articles comme d'habitude, mais les stocker dans la mémoire. Ensuite, pour chaque 100 articles d'effectuer une sélection sur ces 100 articles et d'obtenir une liste des éléments existants dans la base de données qui correspondent. Puis concaténer les insert/update ensemble et de les exécuter dans la base de données. Cela aurait pour effet de couper vers le bas sur les voyages à la base de données.

Est-ce suffisamment évolutive de la solution et il n'existe aucun exemple des tutoriels sur l'importation de gros se nourrit dans un environnement de production?

Grâce

OriginalL'auteur markvpc | 2010-02-26

  1. 14

    De voir que vous êtes à l'aide de SQL Server 2008, je vous recommande cette approche:

    • première bulkcopy de vos fichiers CSV dans une table intermédiaire
    • mise à jour de votre table cible à partir de cette mise en scène de la table à l'aide de la commande de FUSION

    Découvrez la MSDN docs et un excellent post de blog sur la façon d'utiliser la commande MERGE.

    En gros, vous créez un lien entre votre tableau de données et la mise en scène de la table sur des critères communs (par exemple, une commune de la clé primaire), et puis vous pouvez définir quoi faire quand

    • la correspondance de lignes, par exemple, la ligne existe dans la source et la cible tableau --> en général, vous auriez du mettre à jour certains champs, ou tout simplement l'ignorer tous ensemble
    • la ligne à partir de la source n'existe pas dans la cible --> typiquement un cas pour une INSERTION

    Vous auriez une MERGE déclaration à quelque chose comme ceci:

    MERGE TargetTable AS t
USING SourceTable AS src
ON t.PrimaryKey = src.PrimaryKey

WHEN NOT MATCHED THEN
  INSERT (list OF fields)
  VALUES (list OF values)

WHEN MATCHED THEN
  UPDATE
    SET (list OF SET statements)
;

    Bien sûr, la ON clause peut être beaucoup plus si nécessaire. Et bien sûr, votre WHEN déclarations peuvent aussi être plus complexes, par exemple

    WHEN MATCHED AND (some other condition) THEN ......

    et ainsi de suite.

    MERGE est un outil très puissant et très utile nouvelle commande dans SQL Server 2008 - utiliser, si vous le pouvez!

    OriginalL'auteur marc_s

  2. 3

    Votre chemin est la pire solution possible. En général, vous ne devriez pas penser en termes d'une boucle dans les dossiers individuellement. Nous avons l'habitude d'avoir une société intégrée de l'outil d'importation qui parcourt les enregistrements, il faudrait 18-20 heures pour charger un fichier avec plus d'un million d'enregistrements (quelque chose qui n'était pas fréquent quand il a été construit, mais qui est un nombre de fois par jour occurrence maintenant).

    Je vois deux options:
    La première utilisation de l'instruction bulk insert pour charger une mise en scène de la table et de faire ce nettoyage que vous devez faire sur la table. Comment allez-vous déterminer si l'enregistrement existe déjà? Vous devriez être en mesure de construire un ensemble de mise à jour par l'adjonction à la mise en scène de la table sur les champs de déterminer la mise à jour. Souvent j'ai ajouté une colonne de ma table intermédiaire pour l'id de l'enregistrement, il correspond à et peuplée que par le biais d'une requête, puis fait la mise à jour. Alors, vous faire une insertion d'enregistrements qui n'ont pas un id correspondant. Si vous avez trop de dossiers à faire tout à la fois, vous pouvez exécuter dans des lots (oui c'est une boucle), mais rendre les lots considérablement plus grande que 1 enregistrement à la fois (j'ai l'habitude de commencer avec 2000 et basée sur le temps qu'il faut pour cela déterminer si je peux faire plus ou moins dans le lot).

    Je pense que 2008 dispose également d'une instruction merge, mais je n'ai pas encore eu la chance de l'utiliser. Rechercher dans la documentation en ligne.

    L'alternative est d'utiliser SSIS qui est optimisé pour la vitesse. SSIS est une chose complexe, et si la courbe d'apprentissage est raide.

    +1 pour l'utilisation de l'instruction BULK INSERT & FUSIONNER
    Merci pour votre suggestion. La raison pour laquelle je boucle sur chaque élément est parce que j'ai besoin pour effectuer la validation de certains et de la logique de mise en forme avant de l'ajouter à la base de données. Cette relaie à l'utilisateur si il ya des problèmes avec le flux lui-même. J'aime l'idée de fusionner les données, si, je vais regarder à cela.
    Vous pouvez le faire facilement de validation et de mise en forme dans un jeu de mode. En parcourant les dossiers individuels est presque toujours un mauvais choix et vous ne devriez pas envisager de le faire jusqu'à ce que toutes les autres options ont été elimiated.

    OriginalL'auteur HLGEM

  3. 2

    Une façon est de charger un fichier CSV dans une Table de données (ou, plus probablement, un DataReader) et ensuite lot slam dans les résultats à l'aide SqlBulkCopy -

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

    Son très efficace et vous pouvez faire un peu de mappage de colonne. Astuce: lorsque vous mappez des colonnes à l'aide de SqlBulkCopy ils sont sensibles à la casse.

    OriginalL'auteur Kris Krause

  4. 0

    Une autre approche consisterait à écrire un .Net procédure stockée sur le serveur sur le serveur pour fonctionner sur l'ensemble du fichier...

    Seulement si vous avez besoin de plus de contrôle que Kris Krause de la solution bien - je suis un grand fan de garder les choses simples (et réutilisable) où l'on peut...

    OriginalL'auteur Martin Milan

  5. 0

    Avez-vous besoin pour être rouler vos propres ici? Serait-il possible de fournir les données de telle manière que le Serveur SQL server peut utiliser à l'Importation en Vrac pour le charger et ensuite traiter avec des doublons dans la base de données une fois l'importation terminée?

    Quand il s'agit de soulever des poids lourds avec beaucoup de données de mon expérience tend à être que le fait de travailler dans la base de données, autant que possible, est beaucoup plus rapide et moins gourmande en ressources.

    OriginalL'auteur glenatron