您的位置:威尼斯官方网站 > 威尼斯官方网站登录 > 语句没有使用GO来进行批处理拆分

语句没有使用GO来进行批处理拆分

发布时间:2020-01-02 18:07编辑:威尼斯官方网站登录浏览(82)

    作为DBA,时不时会遇到将数据导入到数据库的情况,假设业务或研发提供一个包含上百万行INSERT语句的脚本文件,而且这些INSERT 语句没有使用GO来进行批处理拆分,那么直接使用SQLCMD来执行会发现该进程消耗大量物理内存并迟迟没有数据写入,即使脚本中每一行都添加了GO,你依然会发现这插入效率太差,让你无法忍受,怎么搞呢,下面小代码帮你折腾下:

    $old_file_path= 'C:SQL001.TXT'
    $new_file_path='C:SQL001_New'
    $tran_rows=10
    $file_rows=30
    $current_file_id=0
    
    $line_num=0
    $sr1=[IO.StreamReader]$old_file_path
    $line_content=""
    $row_content=""
    while(-not $sr1.EndOfStream)
    {
        if(($line_num%$file_rows) -eq 0)
        {
            $current_file_id=[int]$current_file_id+1
            $current_file_path = $new_file_path+"_"+$current_file_id
            $line_content=""
            $line_content |Out-File $current_file_path
        }
    
        if(($line_num%$tran_rows) -eq 0)
        {
            $line_content="
    SET NOCOUNT ON
    GO
    BEGIN TRAN
    BEGIN TRY
    "
            $line_content |Out-File -Append $current_file_path
            $line_content=""
        }
        $tmp_content=$sr1.ReadLine()
        $row_content=$row_content+"`r"+$tmp_content
    
        $line_num=$line_num+1
    
        if((($line_num%$tran_rows) -eq 0) -or ($sr1.EndOfStream))
        {
            $row_content | Out-File -Append $current_file_path
            $row_content=""
            $batch_first_line=([int](($line_num+1)/$tran_rows-1))*$tran_rows+1
            $line_content="
    COMMIT
    --PRINT '执行第"+($batch_first_line)+"行到第"+($line_num)+"行成功'
    END TRY
    BEGIN CATCH
    ROLLBACK
    PRINT '执行第"+($batch_first_line)+"行到第"+($line_num)+"行失败'
    END CATCH
    GO
    "
            $line_content | Out-File -Append $current_file_path
            Write-Host "处理到行" $line_num
        }
    
    
    
    }
    
    $sr1.Close()
    

     

    2016年11月2日进行了下优化,当单个文件包含过多行时,会很难编辑处理,增加了按照行数拆分成文件的功能,同时优化下代码的可读性。

    还是看点疗效吧,原始SQL为:

    图片 1

    经过此脚本修改过的变为:

    图片 2

    这样实现有以下有点:

    1.  使用“SET NOCOUNT ON”来屏蔽每条语句返回影响行数,减少与cmd窗口交互的次数;

    2.  将每50条语句拆分到一个批处理中,降低数据库进行语法检查分析的消耗,在封装到一个事务中进行提交,减少写日志的次数;

    3.  打印输出事务执行结果,方便排查错误(可修改代码只输出执行失败的事务)

    执行结果截图:

    图片 3

    ====================================================

    在个人电脑测试,以100条一个事务来拆分,大概1分钟可以导入50万到60万,按不同的行数进行拆分插入效率不同,具体合适的拆分行数需要根据实际情况测试为准。

    对于超大数据量的导入,还是推荐使用csv+bcp的方式来导入,INSERT+SQLCMD的效率始终太低太低!

    ====================================================

    没啥技术含量,厚脸拿出来分享,只是因为很久没写博客啦!

    图片 4

    本文由威尼斯官方网站发布于威尼斯官方网站登录,转载请注明出处:语句没有使用GO来进行批处理拆分

    关键词: