sql-server - SQL Server 8 - 加速插入

Question

是否可以在 SQL Server 8 中为此插入加速 INSERT INTO - 我已将查询匿名化，这是一个非常简单的语句。

每个项目都不同——有时我需要插入 5k 行，有时我必须插入 250k-600k 行——这完全取决于我的要求。较大的插入需要很长时间。

当需要更大的 Blade 时，有没有办法加快速度？

Declare @MyVariable_I as int
Set @MyVariable_I = 15


INSERT INTO ExistingTable 
            -- there is a field in this table called UID that has a clustered index on it
            -- Currently has 106.8 Million rows in it
            -- Only has 7 fields in it and they're small fields
(
    Field1            -- non-unique, non-clustered on this field already exists
    ,Field2
    ,Field3            -- non-unique, non-clustered on this field already exists
    ,Field4            -- non-unique, non-clustered on this field already exists
    ,Field5            -- non-unique, non-clustered on this field already exists
    ,Field6
    ,Field7
)
    SELECT
        Field1
        , @MyVariable_I
        ,Field3
        ,0
        ,Field5
        , NULL
        ,Field7
    FROM
        SomeOtherTable WITH (nolock) 
        -- can have anywhere from 5,000 to 250,000 rows - each project is different

-- 好的，这是我对如何进行批量插入的猜测
-- 在这里找到这个项目: http://sqlserverplanet.com/data-warehouse/transferring-large-amounts-of-data-using-batch-inserts

[cc lang=”sql”]
DECLARE @BatchSize int = 10000

SELECT 1                      -- @Larnu
WHILE @@ROWCOUNT > 0          -- @Larnu
BEGIN

INSERT INTO [dbo].[Destination] –WITH (TABLOCK) — Uncomment for 2008
(
    Field1   
    ,Field2
    ,Field3  
    ,Field4  
    ,Field5  
    ,Field6
    ,Field7
)
SELECT TOP(@BatchSize)
    SELECT
        Field1
        , @MyVariable_I
        ,Field3
        ,0
        ,Field5
        , NULL
        ,Field7
    FROM
        SomeOtherTable SOT
    ORDER BY                                      -- @Larnu
        FIELD1,FIELD2,FIELD3                      -- @Larnu
    WHERE
        NOT EXISTS (                              -- @Larnu FROM HERE DOWN
            SELECT 1
            FROM dbo.Destination DEST
            WHERE 
                DEST.FIELD1 = SOT.FIELD1 AND 
                DEST.FIELD2 = SOT.FIELD2 AND
                DEST.FIELD3 = SOT.FIELD3 
                    )

--commented next item as it is not needed due to @Larnu suggestions
--IF @@ROWCOUNT < @BatchSize BREAK 

END 
[/cc] 

Answer 1

我不确定这是否仍然需要答案，但我最近在将大量数据从一个服务器的重复表移动到另一台服务器时遇到了类似的问题。我已经修改了我为您的示例所做的工作。

SET NOCOUNT ON;
DECLARE @MyVariable_I AS INT = 15

insert_more_rows:
INSERT INTO ExistingTable (
    Field1, Field2, Field3, Field4, Field5, Field6, Field7
)
SELECT TOP 100
    Field1
    , @MyVariable_I
    , Field3
    , 0
    , Field5
    , NULL
    , Field7
FROM
    SomeOtherTable WITH ( NOLOCK )
WHERE 
    Field1 > ( SELECT MAX( Field1 ) FROM ExistingTable )
ORDER BY
    Field1;

IF EXISTS ( SELECT * FROM SomeOtherTable WHERE Field1 > ( SELECT MAX( Field1 ) FROM ExistingTable ) )
    GOTO insert_more_rows;

这个想法是将插入分成可管理的“批次”，这样您就不会在服务器运行时杀死服务器并锁定数据库。

在这种情况下，我假设“Field1”是插入到“ExistingTable”中的“SomeOtherTable”中的唯一ID。有了这个假设并在 Field1 上对我的 SELECT 进行排序，我可以通过将其 Field1 最大值与 SomeOtherTable.Field1 值进行比较来确定是否需要将更多记录插入到 ExistingTable 中。然后，如果还有更多行要插入， GOTO insert_more_rows被调用，插入另外 100 行。