Я просто столкнулся с этой проблемой ... Таблица с 447 732 310 записями в ней. Если бы коллега придумал удивительное решение, потребовалось бы около 24 минут для копирования данных в новую таблицу, около 40 минут для создания индексов.
Вот что мы сделали:
--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';