我正在运行一个 ETL 进程,它将大约 200 万行写入 SQL Server 数据库。
我正在尝试优化纯插入所需的时间(我猜更新是另一回事)。
我想知道对于 SQL 数据库的基本插入,最大的瓶颈是什么,或者减少时间的最佳方法。
我的意思是,第一件事可能是数据的大小,对吧?行数、列数和每列中的数据大小。其中一些可能无法最小化,每行的 KB/ 占用空间是可以优化的一件事,对吧?
还有什么可以优化的或者是最大的因素?是传输介质吗?我的意思是,写入同一台计算机上的数据库与通过 Web 连接写入(即健壮、快速且 ping 为 1 毫秒?)之间有多大差异。
最后 --- 为什么到数据库的多个并行连接似乎加快了进程到一个点?我的意思是,当我有 20 个连接以循环方式插入时,它比一个写入所有数据的连接快 6-7 倍。我很好奇这是为什么。
现在我有 220 万行,总计 2.7 GB。这是每行 1.23 kb。
现在使用 14 个连接一次插入 1000 行(1.23 MB)需要 6.7 秒。这是每秒 10.66 行的蜗牛节奏。即使假设 1 个连接也一样快(它不是),最多 150 行/秒,这也不是完全“快”。我正在编写一个超快速、强大的 Web 连接 b/c 我们不能在与数据仓库相同的空间上拥有 ETL 过程。
那么..我如何在这里优化速度?
一次 1000 行的原因是因为数据来自 1000 页 - 但优化解析现在是一个单独的问题。
我确实有一个我相信的主索引,但没有什么太昂贵的。现在我只是在做蒙特卡洛之类的测试(尝试一下,看看有什么用),但我需要一些更专注的东西。
您需要阅读:
您必须阅读每个链接。真的。简而言之,高效加载必须使用批量插入并实现最少的日志记录。SSIS 是迄今为止更好的工具,但您也可以通过编程方式实现这一点。OleDB 是最好的,但如果需要,SqlClient (C#) 也可以。行大小、网络速度之类的东西在这里不太可能成为您关心的问题,但您应该始终衡量(链接的文章会教您如何衡量)。加载性能应该与现有的数据库大小完全正交,如果加载速度随着数据库大小的增加而降低,那么您一定是做错了。
由于缺乏任何调查和测量,我会猜测。但很可能您现在被提交刷新率阻塞:每次您提交 SQL 时都必须停止并等待日志写入磁盘。如果您没有明确地开始事务,那么每个语句都必须停止并等待。添加更多的加载器会导致更好的日志利用率,日志一次提交给 20 个写入者。阅读什么是 LSN:日志序列号。