Lot de s'engager sur la grande opération d'INSERTION dans SQL natif?

J'ai un couple de grandes tables (188 m et 144m lignes) j'ai besoin de remplir à partir de points de vue, mais chaque vue contient quelques centaines de millions de lignes (extraction de l'ensemble des pseudo-dimensions de la modélisation des données dans un formulaire plat). Les touches sur chaque table sont plus de 50 composite octets de colonnes. Si les données dans les tables, je pensais toujours à propos de l'utilisation de sp_rename pour faire l'autre table, mais ce n'est pas vraiment une option.

Si je fais une seule opération d'INSERTION, le processus utilise une énorme quantité d'espace du journal des transactions, typicalyl de dépôt et en invitant un tas de tracas avec les Administrateurs de bases de données. (Et oui, c'est probablement un travail les Administrateurs de base de données doit gérer/conception/architecte)

Je peux utiliser SSIS et diffuser les données dans la table de destination avec la validation du lot (mais cela ne veut exiger la transmission des données par le réseau, car nous ne sommes pas autorisés à exécuter des packages SSIS sur le serveur).

Toutes les autres choses que de diviser le processus en plusieurs opérations d'INSERTION à l'aide de une sorte de clé pour distribuer les lignes dans les différents lots et faire une boucle?

  • Si la table de partitionnement est une option, vous pouvez diviser les inserts par la partition de la valeur. Pourrait la rendre plus rapide à assembler résultant des sous-ensembles.
InformationsquelleAutor Cade Roux | 2009-10-21
  1. 5

    Vous pouvez partitionner vos données et insérez vos données dans un curseur de la boucle. Ce serait presque la même que SSIS batchinserting. Mais s'exécute sur votre serveur.

    create cursor ....
select YEAR(DateCol), MONTH(DateCol) from whatever

while ....
    insert into yourtable(...)
    select * from whatever 
    where YEAR(DateCol) = year and MONTH(DateCol) = month
end
    • C'est finalement la stratégie que j'ai utilisée.
    InformationsquelleAutor Arthur
  2. 5

    Ne l'ont pas vue sur TOUT type d'identificateur unique /candidat à la clé? Si oui, vous pouvez sélectionner les lignes dans une table de travail à l'aide de:

    SELECT key_columns INTO dbo.temp FROM dbo.HugeView;

    (Si il est logique, peut-être mettre cette table dans une autre base de données, peut-être avec le modèle de récupération SIMPLE, pour empêcher le journal de l'activité d'interférer avec votre base de données primaire. Cela devrait générer beaucoup moins de journaux de toute façon, et vous pouvez libérer de l'espace dans l'autre base de données avant de reprendre, dans le cas où le problème est que vous avez un manque d'espace disque tout autour.)

    Ensuite, vous pouvez faire quelque chose comme cela, l'insertion de 10 000 lignes à la fois, et de sauvegarder le journal entre:

    SET NOCOUNT ON;

DECLARE
    @batchsize INT,
    @ctr INT,
    @rc INT;

SELECT
    @batchsize = 10000,
    @ctr = 0;

WHILE 1 = 1
BEGIN
    WITH x AS
    (
        SELECT key_column, rn = ROW_NUMBER() OVER (ORDER BY key_column)
        FROM dbo.temp
    )
    INSERT dbo.PrimaryTable(a, b, c, etc.)
        SELECT v.a, v.b, v.c, etc.
        FROM x
        INNER JOIN dbo.HugeView AS v
        ON v.key_column = x.key_column
        WHERE x.rn > @batchsize * @ctr
        AND x.rn <= @batchsize * (@ctr + 1);

    IF @@ROWCOUNT = 0
        BREAK;

    BACKUP LOG PrimaryDB TO DISK = 'C:\db.bak' WITH INIT;

    SET @ctr = @ctr + 1;
END

    C'est tout en haut de ma tête, afin de ne pas couper/coller/exécuter, mais je pense que l'idée générale est là.

    Noter que si vous prenez de base de données régulièrement des sauvegardes des journaux et vous aurez probablement envie de prendre plein de commencer votre journal de la chaîne une fois de plus.

    • C'est drôle, parce qu'avant j'ai construit la plupart de ce système dans le serveur, et était toujours à l'aide de ma boîte à développer, j'avais déjà divisée catégories de tables pour les objectifs de valorisation et de les mettre dans des schémas distincts dans l'espoir que les Administrateurs de base de données pourrait me donner de multiples bases de données, mais il ne devait pas être...
    • Et, malheureusement, les touches sont toutes plus de 50 octets de colonnes composites, suppose qu'il est temps pour le faire...
    • Je sais que c'est vieux, mais j'ai simplement utilisé ce test. La théorie fonctionne très bien, vous êtes manque juste SET @ctr = @ctr + 1 dans la boucle pour incrémenter le numéro de lot du compteur. J'espère que vous n'avez pas l'esprit - j'ai édité (après essais les résultats).
    InformationsquelleAutor Aaron Bertrand
  3. 3

    Je sais que c'est un vieux thread, mais j'ai fait une version générique de Arthur curseur solution:

    --Split a batch up into chunks using a cursor.
--This method can be used for most any large table with some modifications
--It could also be refined further with an @Day variable (for example)

DECLARE @Year INT
DECLARE @Month INT

DECLARE BatchingCursor CURSOR FOR
SELECT DISTINCT YEAR(<SomeDateField>),MONTH(<SomeDateField>)
FROM <Sometable>;


OPEN BatchingCursor;
FETCH NEXT FROM BatchingCursor INTO @Year, @Month;
WHILE @@FETCH_STATUS = 0
BEGIN

--All logic goes in here
--Any select statements from <Sometable> need to be suffixed with:
--WHERE Year(<SomeDateField>)=@Year AND Month(<SomeDateField>)=@Month   


  FETCH NEXT FROM BatchingCursor INTO @Year, @Month;
END;
CLOSE BatchingCursor;
DEALLOCATE BatchingCursor;
GO

    Cela a résolu le problème sur les charges de nos grandes tables.

    InformationsquelleAutor QuickDraw
  4. 2

    Il n'y a pas de poussière de lutin, tu le sais.

    Sans connaître les détails au sujet de la réelle schéma de transfert, une solution générique serait exactement comme vous le décrire: fracture de la transformation en multiples insertions et de garder trace de la clé(s). C'est une sorte de pseudo-code T-SQL:

    create table currentKeys (table sysname not null primary key, key sql_variant not null);
go

declare @keysInserted table (key sql_variant);
declare @key sql_variant;
begin transaction
do while (1=1)
begin
    select @key = key from currentKeys where table = '<target>';
    insert into <target> (...)
    output inserted.key into @keysInserted (key)
    select top (<batchsize>) ... from <source>
    where key > @key
    order by key;

    if (0 = @@rowcount)
       break; 

    update currentKeys 
    set key = (select max(key) from @keysInserted)
    where table = '<target>';
    commit;
    delete from @keysInserted;
    set @key = null;
    begin transaction;
end
commit

    Il aurait plus compliqué si vous souhaitez autoriser parallèle lots et partition les touches.

    • En fait, au sol jusqu'à balles d'argent font d'excellents poussière de lutin
    • ty m. Kelley, je vais garder cela à l'esprit lors de mon prochain projet 😉
    InformationsquelleAutor Remus Rusanu
  5. 1

    Vous pouvez utiliser la commande BCP pour charger les données et l'utilisation de la Taille de Lot paramètre

    http://msdn.microsoft.com/en-us/library/ms162802.aspx

    Processus en deux étapes

    • BCP de données à partir de points de Vue dans des fichiers Texte
    • BCP DE données à partir de fichiers Texte dans des Tableaux avec la taille des lots paramètre
    • 13 secondes! secoue minuscule poing
    • Comment avez-vous eu l'secondes de la partie? Il ne me montre minutes
    • passez la souris sur la relative horodatages pour obtenir de réels horodateurs avec secondes
    • il modifie les minutes puis les heures puis les jours que le temps passe. assez lisse, jamais pensé à ça avant
    InformationsquelleAutor Raj More
  6. 1

    Cela ressemble à un travail pour de bon ol' BCP.

    InformationsquelleAutor Chris McCall