8
votes

Performances SQL Server pour Alter Tableau Alter Colonne Change Type de données

Nous devons modifier les types de données de certaines colonnes de Int vers Bigint. Malheureusement, certaines de ces tables sont grandes, environ 7 à 10 millions de lignes (mais pas large).

Alter table Alter colonne prend pour toujours sur ces tables. Y a-t-il un moyen plus rapide d'y parvenir?

performance sql sql-server

0 commentaires

4 Réponses :

2
votes

Créer la nouvelle table que vous désirez, avec les bons colonnes et indices. (Écrivez l'ancienne table et changez le nom.)

Insérer dans la nouvelle table (liste de colonnes) Sélectionnez * à partir de Old_Table;

renommer l'Old_table Old_Table_back, renommer nouvellement_dable Old_Table.

Créer les anciens indices sur la nouvelle table, déposez toutes les contraintes de RI sur l'ancienne table et les croyez-les sur la nouvelle table. Encore une fois, votre RDBMS aura un moyen facile de générer des scripts pour le faire.

2 commentaires

Sélectionne dans une nouvelle table dans une colonne avec un type de données différent tout plus rapide que de modifier le type de données d'une colonne existante? Nous le faisons pour les colonnes de clés primaires.

Généralement beaucoup plus rapide; Plutôt que de devoir redimensionner chaque rangée, il suffit de créer de nouvelles lignes de la bonne taille.

9
votes

Par coïncidence, je devais faire quelque chose de très similaire il y a 3 heures. La table était de 35 millions de lignes, c'est assez large, et il prenait pour toujours pour le faire:

alter table myTable add myNewColumn int null;

while 1=1
begin
    update top (100000) myTable
    set
        myNewColumn = 0
    where
        myNewColumn is null;

    if @@ROWCOUNT = 0 break;
end

alter table myTable alter column myNewColumn int not null;
alter table myTable add constraint tw_def_myNewColumn default (0) for myNewColumn;

2 commentaires

Je n'ai trouvé aucune différence de performance entre le changement de la taille de la colonne et la copie dans une nouvelle colonne dans une table avec 10 millions de rangées + rangées.

Ce qui signifie qu'il était lent dans les deux cas?

0
votes

Je viens de courir dans ceci il y a quelques semaines avec une table avec des rangées de 639 m. J'ai fini par créer une nouvelle table et copier les données dans "lots". Il a fallu environ 2 jours sur le serveur principal et la réplication a pris 3 jours pour la répliquer. J'ai ensuite modifié toutes les vues et les Procs qui utilisaient une table ancienne. Cela m'a permis de nettoyer quelques problèmes, comme se débarrasser d'une colonne que je ne voulais pas et que je crains (dans certains cas) de meilleurs indices. Une fois que toutes les données ont été déplacées, SQL a changé, j'ai ensuite laissé tomber l'ancienne table.

Un gâchis, mais je suis plus sage pour cela maintenant. Utilisez BIG INTS pour vos clés principales d'identité Si le système sera vécu depuis longtemps et il y a une chance de multi-millions de lignes.

0 commentaires

1
votes

Je viens de courir sur ce problème ... une table avec 447 732 310 enregistrements dedans. Avait un collègue de collègue proposer une solution géniale, n'a pris environ 24 minutes) environ 24 minutes pour copier des données vers une nouvelle table, environ 40 minutes pour créer des index.

Voici ce que nous avons fait: P>

--Get ntiles of idOrders, split up into 100 groups - 1:20 minutes
IF(OBJECT_ID('TEMPDB..#x')) IS NOT NULL
    DROP TABLE #x

SELECT nt, MIN(idOrder) idOrderMin, MAX(idOrder) idOrderMax
INTO #X
FROM (
       SELECT idOrder, NTILE(100) OVER(ORDER BY idOrder) nt
       FROM (
              SELECT DISTINCT idOrder FROM order_raw_fields
       ) X
) Y
GROUP BY nt

-- view results
--SELECT * FROM #x ORDER BY idOrderMin

-- create new table
SELECT TOP 0 * 
INTO ORDER_RAW_FIELDS_Intl 
FROM ORDER_RAW_FIELDS

ALTER TABLE dbo.ORDER_RAW_FIELDS_Intl
    ALTER COLUMN value nvarchar(500)

--Build queries
SELECT 'insert into ORDER_RAW_FIELDS_Intl select * from order_raw_fields  
where idOrder >= ' + CAST(idOrderMIn AS VARCHAR(100)) + ' and idOrder <= ' + CAST(idOrderMax AS varchar) InsertStmt
    INTO #inserts
FROM #X 
ORDER BY idOrderMin

DECLARE insertCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT InsertStmt
    FROM #inserts

OPEN insertCursor

-- 24:04 minute execution time to match
DECLARE @insertStmt NVARCHAR(125)
FETCH NEXT FROM insertCursor INTO @insertStmt
WHILE @@FETCH_STATUS = 0
BEGIN
    --EXECUTE @insertStmt
    EXECUTE sp_executesql @statement=@insertStmt

    PRINT 'Execution Complete:  ' + @insertStmt

    FETCH NEXT FROM insertCursor INTO @insertStmt   
END

CLOSE insertCursor
DEALLOCATE insertCursor

-- Add indexes
-- 21:37 minutes completion time
ALTER TABLE [dbo].[ORDER_RAW_FIELDS_Intl] ADD  CONSTRAINT [PK_ORDER_RAW_FIELDS_Intl] PRIMARY KEY CLUSTERED 
(
    [idRow] ASC,
    [idOrder] ASC,
    [remoteFieldName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 92) ON [PRIMARY]
GO

-- 13:45 minutes completion time
CREATE NONCLUSTERED INDEX [IX_idOrder_remoteFieldName2] ON [dbo].[ORDER_RAW_FIELDS_Intl]
(
    [idOrder] ASC,
    [remoteFieldName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 94) ON [PRIMARY]
GO

-- drop table 
TRUNCATE TABLE [dbo].[ORDER_RAW_FIELDS]
DROP TABLE [dbo].[ORDER_RAW_FIELDS]

-- renamed new table to old tables's name
EXEC sp_rename 'ORDER_RAW_FIELDS_Intl', 'ORDER_RAW_FIELDS';

0 commentaires