J'ai quelques grandes tables (rangées de 188m et 144 m), je dois remplir des vues, mais chaque vue contient quelques centaines de millions de lignes (tirant ensemble des données de manière pseudo-dimensionnelle dans une forme plate). Les touches de chaque table comptent plus de 50 octets composites de colonnes. Si les données étaient dans des tables, je pouvais toujours penser à utiliser sp_rename pour faire l'autre nouvelle 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 de journal de transaction, le dépôt typique de la transaction et invite un tas de tracas avec les DBA. (Et oui, c'est probablement un travail que les DBAS doivent gérer / concevoir / architecte)
Je peux utiliser SSIS et diffuser les données dans la table de destination avec les engagements par lots (mais cela nécessite que les données soient transmises sur le réseau, car nous ne sommes pas autorisés à exécuter des packages SSIS sur le serveur).
Toute autre chose que de diviser le processus en plusieurs opérations d'insertion en utilisant une sorte de clé pour distribuer les lignes dans différents lots et faire une boucle?
6 Réponses :
Vous pouvez partitionner vos données et insérer vos données dans une boucle de curseur. Ce serait presque la même chose que SSIS batatoire. Mais fonctionne 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'était finalement la stratégie que j'ai utilisée.
Vous pouvez utiliser la commande BCP pour charger les données et utiliser le paramètre de taille de lot
http://msdn.microsoft.com/en-us/library /ms162802.aspx
processus de deux étapes
13 secondes! Shakes Tiny Fist
@Raj: Survolez les horodatages relatives pour obtenir des horodatages réels avec des secondes
Cela change en minutes puis des heures puis des jours au fil du temps. jolie lisse, jamais pensé à ce sujet avant
Il n'y a pas de poussière de pixie, vous savez que.
Sans connaître des détails sur le schéma réel étant transféré, une solution générique serait exactement comme vous le décrivez: diviser le traitement en plusieurs inserts et garder une trace de la clé (S ). Ceci est une sorte de pseudo-code T-SQL: Il serait plus compliqué si vous souhaitez autoriser des lots parallèles et partitionner les touches.
La vue a-t-elle un type d'identifiant / clé candidat unique? Si tel est le cas, vous pouvez sélectionner ces lignes dans une table de travail à l'aide de: (si c'est logique, mettez peut-être cette table dans une base de données différente, peut-être avec un modèle de récupération simple, pour empêcher le journal. activité d'interférer avec votre base de données principale. Cela devrait générer beaucoup moins de journal de toute façon, vous pouvez libérer de l'espace dans l'autre base de données avant de reprendre, au cas où le problème est que vous avez un espace disque inadéquat tout autour.) P> Ensuite, vous pouvez faire quelque chose comme ceci, insérant 10 000 rangées à la fois et sauvegarder la connexion entre: P> Notez que si vous prenez une base de données régulière et que vous connectez des sauvegardes de connexion, vous voudrez probablement s'apercer pour démarrer votre chaîne de journaux. P> P>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 drôle, car avant d'avoir construit la majeure partie de ce système sur le serveur et que j'utilisais toujours ma boîte pour développer, j'avais déjà divisé les catégories de tables pour les objectifs de récupération et les mettre dans des schémas séparés dans l'espoir que le DBAS me donnerait plusieurs bases de données, mais ce ne devait pas être ...
Et malheureusement, les clés sont sur 50 octets de colonnes composites, devinez qu'il est temps de le faire faire ...
@Aaronbertrand Je sais que c'est vieux, mais je viens d'utiliser cela dans un test. La théorie fonctionne bien, vous êtes simplement manquant Set @ctr = @ctr + 1 dans la boucle pour incrémenter le compteur de numéro de lot. J'espère que cela ne vous dérange pas - je l'ai modifié (après avoir testé les résultats).
Pouvez-vous me dire pourquoi vous sauvegardez le journal, entre chaque itération du curseur?
@Josh Voir SQLperformance.com/2013/03/IO-SUBSystem/chunk-Deeletes - essentiellement, vous pouvez échanger la durée de la croissance du journal. Sauvegarder le journal prend souvent un peu plus longtemps mais a un impact beaucoup plus faible sur le fichier journal lui-même. Et vous n'avez pas à le faire sur chaque itération, vous pouvez trouver un certain équilibre entre 100 itérations ou toutes les 1 000 itérations ou quelles avez-vous, en fonction de votre taille de lot et d'autres facteurs. Le point doux pourrait être différent sur un système donné.
Je sais que c'est un vieux fil, mais j'ai fait une version générique de l'Arthur's Cursor 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
Si la partitionnement de la table est une option, vous pouvez diviser les inserts de la valeur de la partition. Pourrait rendre plus rapide de coudre les sous-ensembles résultants.