我的任务是开发一种方法,用于将具有100万行或更多行的文本文件导入到表中。表中的每一行是856字节,分成大约150个“列”数据。我正在使用SQL Server 2012标准版。与插入相关的活动是数据库中唯一的活动。我已经构建了一个执行以下操作的存储过程:

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

将原始文本文件大容量插入临时表的速度足够快,不到1分钟。但是,我需要解析原始数据并将其放入目标表中。很明显,这意味着,例如,来自行位置1-10的数据应该写入目标表中,11-13应该写入目标表中,等等。我使用while循环而不是游标,循环遍历100万行中的每一行,然后将原始数据作为参数传递给另一个存储过程,该存储过程将解析原始数据并插入到目标表中。我只是使用substring函数来解析原始数据,即substring(@raw data,1,10)。
正如我提到的,每行中大约有150个字段。然而,解析/写入步骤的性能非常糟糕。进口17万行已经花了10个小时。
目标表上没有聚集索引。恢复模型设置为simple。我启用了“即时文件初始化”。
我正在考虑是否可能使用ssis,但1)我怀疑性能可能不会更快,2)ssis中的故障排除似乎更麻烦。
寻找我忽略的想法,聪明的想法和明显的建议。
编辑(在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
    ...
    )

最佳答案

不要使用while循环,而是考虑一个基于集合的解决方案,在这个解决方案中,您不必分别处理每一行。
如果您可以共享更多的while循环中的代码信息,我们可能会大大加快它的速度。即使不使用ssis;-)
目前我不同意有一个“需要”来解析一行…

09-30 15:20