SQL Server批量插入超过100万行-需要改进性能

Question

我的任务是开发一种方法，将具有100万或更多行的文本文件导入到表中。表中的每一行为856字节，分为大约150列数据。我使用的是SQL Server 2012 Standard Edition。与INSERT相关的活动是数据库中唯一的活动。我构建了一个执行以下操作的存储过程：

GET LIST OF UNIMPORTED DATA FILES IN A SPECIFIC DIRECTORY
LOOP THROUGH LIST OF FILES
    BULK INSERT INDIVIDUAL FILE DATA INTO (DISK-BASED) TEMP TABLE
    LOOP THROUGH RECORD-LEVEL DATA IN TEMP TABLE
    PARSE/WRITE RECORD-LEVEL DATA TO DETAILRECORD TABLE
    IF ALL RECORDS FOR A GIVEN FILE ARE SUCCESSFULLY WRITTEN TO THE DETAIL RECORD TABLE
        THEN 
            MOVE FILE TO IMPORTED_FILES DIRECTORY
            WRITE SUCESS RECORD TO IMPORTLOG TABLE
        ELSE
            WRITE FAIL RECORD TO IMPORTLOG TABLE
            MOVE ON TO NEXT FILE

将原始文本文件BULK插入TEMP表的速度足够快，不到1分钟。但是，我需要解析原始数据，并将数据放入其目标表中。需要明确的是，这意味着，例如，来自行位置1-10的数据应该写入目标表，11-13应该写入目标表格，等等。我通过使用WHILE循环而不是CURSOR，并循环通过100万行中的每一行，然后将原始数据作为参数传递给另一个存储过程，该存储过程随后解析原始数据并插入到目标表中。我只是通过使用SUBSTRING函数来解析原始数据，即SUBSTRING（@rawdata，1,10）。正如我提到的，每行中大约有150个字段。然而，解析/写入步骤的性能非常糟糕。导入170K行需要10个小时。

目标表上没有聚集索引。恢复模型设置为SIMPLE。我已启用"即时文件初始化"。

我正在考虑是否可能使用SSIS，但1）我怀疑性能可能不会快很多，2）SSIS中的故障排除似乎更麻烦。

寻找我忽略的想法、绝妙的想法和显而易见的建议。

EDIT（在WHILE循环中添加代码）：

WHILE @RecordCounter <= @FileRecordCount
BEGIN
    SELECT @Record = record FROM CDR.tempFile WHERE id = @RecordCounter                                 
    EXEC [CDR].[usp_ImportCDRData_Record] @pRecord = @Record, @pCarrier = @carrier, @pLogid = @ImportLogID
    SET @RecordCounter = @RecordCounter + 1
END
--Inside [CDR].[usp_ImportCDRData_Record] 
INSERT INTO dbo.DetailRecord
    ( 
    LOGID ,
    ACCOUNTNUMBER ,
    CORPID ,
    SERVICELOCATION ,
    NETWORKINDICATOR ,
    ...
    )
VALUES
    ( 
    @pLogID,
    SUBSTRING(@pRecord, 1,10), -- ACCOUNTNUMBER
    SUBSTRING(@pRecord, 11,8), -- CORPID
    SUBSTRING(@pRecord, 19,8), -- SERVICELOCATION
    SUBSTRING(@pRecord, 27,1), -- NETWORKINDICATOR
    ...
    )

Answer 1

不要使用WHILE循环，而是考虑一个基于集合的解决方案，在该解决方案中，您不必单独处理每一行。

如果您能分享WHILE循环中的代码的更多信息，我们可能会大大加快它的速度。即使不使用SSIS；-）

目前我不同意"需要"一次解析一行。。。