如何在异步和线程中执行大量 sql 查询

问题:我有大量的 sql 查询 (大约 10 k-20 k),我想在 50 个 (或更多) 线程中异步运行它们。

我为这项工作写了一个 powershell 脚本,但是速度很慢 (执行所有脚本大约需要 20 个小时)。期望的结果是最多 3-4 小时。

问题:如何优化 powershell 脚本?我是否应该重新考虑并使用另一种技术python or c#

我认为这是 powershell 的问题,因为当我检查时whoisactive查询执行得很快。创建、退出和卸载作业需要大量时间,因为每个线程都创建了单独的 PS 实例。

我的代码:

$ NumberOfParallerThreads = dec;


$ Arr _ all queries = @ ('exec [mystoredproc] @ param1 = 1,@ param2 = 2,
'Exec [mystoredproc] @ param1 = 11,@ param2 = 22',
'Exec [mystoredproc] @ param1 = 111,@ param2 = 22')

# 创建批次
$ 专柜 = [pscustomobject 】 @ {价值 = 月};
$ Batches_ all queries = $ arr _ all queries | 组-对象-属性 {
[数学]:: 地板 ($ 专柜.值 +/$ NumberOfParallerThreads)
};

的 forEach ($ $ Batches_AllQueries 内) {
$ Tmbatch = $ item.Group;

$ Tmbatch | % {

$ ScriptBlock = {
# 接受跨越作业上下文障碍的循环变量
Param ($ 查询)
# 执行命令

试试
{
写主机 “[处理 '$ query']”
$ ObjConnection = 新对象系统.Data.SqlClient.SqlConnection;
$ ObjConnection.ConnectionString = '数据源 =..';

$ ObjCmd = New-Object System.Data.SqlClient.SqlCommand;
$ ObjCmd.Command text = $ query;
$ ObjCmd。连接 = $ objConnection;
命令超时 = 0;

$ Obj适配器 = 新对象系统.数据.SqlClient.Sqldata适配器;
$ ObjAdapter。SelectCommand ObjCmd = $;
$ ObjDataTable = 新对象系统的数据.DataTable;
$ ObjAdapter 填 ($ objDataTable) | 出空;

$ ObjConnection.Close ();
$ ObjConnection = $ null;
}
抓
{
$ Error Message = $ _。异常。消息
$ FailedItem = $ _。异常。项目名称
【错误处理: $ ($ query) 】】-背景颜色红色;
写主机 $ 错误消息
}

}

# 通过作业上下文屏障传递循环变量
开始作业 $ ScriptBlock-参数列表 $ _ | Out-Null
}

# 等待全部完成
{开始-睡眠 2} 同时 (获取作业状态 “运行”)

# 显示所有作业的输出
Get-Job | 接收-Job | Out-Null

# 清理
移除作业 *

}

更新:

资源:DB 服务器在远程机器上,具有:

  • 24gb 内存
  • 8 芯,
  • 500 GB 存储
  • SQL 服务器 2016

我们希望使用最大的 cpu 功率。

框架限制:唯一的限制是 使用 SQL Server执行查询。请求应该来自外部来源,如 Powershell 、 C # 、 Python 等。

第1个答案

RunspacePool 是去这里的路,试试这个:

$ All queries = @ (.)
$ MaxThreads = 月

# 每个线程都有自己的连接,但是共享查询队列
$ ScriptBlock = {
参数 ($ 工作队列)

$ ObjConnection = 新对象系统.数据.SqlClient.SqlConnection
$ ObjConnection.ConnectionString = '数据源 =..'

$ ObjCmd = 新对象系统.数据.SqlClient.SqlCommand
$ ObjCmd。连接 objConnection = $
$ ObjCmd。CommandTimeout = 月

$ Query = ""

当 ($ WorkQueue.TryDequeue ([ref] $ query) {
$ ObjCmd.Command text = $ query
$ Obj适配器 = 新对象系统。数据。SqlClient。
$ ObjDataTable = 新对象系统的数据.DataTable
$ ObjAdapter 填 ($ objDataTable) | 不为空,
}

$ ObjConnection.Close ()

}

# 创建一个池
$ 泳池 = [RunspaceFactory]:: CreateRunspacePool (月、 $ MaxThreads)
$ 游泳池公寓 = 'sta'
$ Pool.Open ()

# 将查询数组转换为并发队列
$ WorkQueue = 新对象系统。集合。并发。Concurrent 队列 [对象]
$ All queries | % {$ workQueue.Enqueue ($ _)}

$ Threads = @ ()

# 创建每个 powershell 线程并将其添加到池中
$ MaxThreads | % {
$ Ps = [powershell]:: Create ()
$ Ps.RunspacePool = $ pool
$ Ps.AddScript ($ ScriptBlock) | Out-Null
$ Ps.AddParameter (“工作队列”,$ 工作队列) | Out-Null
$ 线程 + = [pscustomobject 】 @ {
Ps = $ ps
手柄 = $ null
}
}

# 启动所有线程
$ 线程 | % {$ _.Handle = $ _.Ps.BeginInvoke ()}

# 等待所有线程完成-错误仍然会设置 IsCompleted 标志
$ Threads |?{!$ _.拉手.IsCompleted}) {
开始-睡眠-秒 1
}

# 获取任何结果并显示错误
$ Threads | % {
$ _.Ps.EndInvoke ($ _.Handle) | 写输出
如果 ($ _.Ps.HadErrors) {
$ _.Ps.Streams.Error.ReadAll () | 写入错误
}
}

与 powershell 作业不同,runspace ols 可以共享资源。因此,所有查询都有一个并发队列,每个线程都保持自己与数据库的连接。

正如其他人所说的 -- 除非你对数据库进行压力测试,否则最好将查询重新组织成批量插入。

第2个答案

您需要重新组织脚本,以便在每个工作线程中保持数据库连接打开,并将其用于该线程执行的所有查询。现在,您正在为每个查询打开一个新的数据库连接,这增加了大量开销。消除这种开销应该可以加快目标的速度。

第3个答案

尝试使用SqlCmd.

可以使用运行多个进程Start.Start ()并使用 sqlcmd 在并行进程中运行查询。

当然,如果你有义务在线程中这样做,这个答案将不再是解决方案。

第4个答案
  1. 根据表和该表上的操作对查询进行分组。 使用此功能,您可以确定可以对不同的表运行多少异步 sql 查询。
  2. 确保要运行的每个表的大小。 因为如果 table 包含数百万行,并且您也与其他表一起执行连接操作会增加时间,或者如果是 CUD 操作,那么您也可能会锁定表。
    1. 还可以根据 CPU 内核而不是假设来选择线程数量。因为 CPU 核心会一次运行一个进程,所以你可以更好地创建芯数 * 2线程是高效的。

因此,首先研究你的数据集,然后做上面的两个项目,这样你就可以很容易地识别所有查询都是并行高效运行的。

希望这能提供一些想法。更好的是,您可以为此使用任何 python 脚本,这样您就可以轻松地触发多个进程,并监控它们的活动。

第5个答案

可悲的是,我现在没有时间完全回答这个问题,但是这应该会有所帮助:

首先,你不会使用整个 CPU 来插入几乎承诺的那么多记录。但是!

由于您似乎正在使用 SQL 字符串命令:

  1. 将插入件拆分为 say ~ 100-~ 1000 组,并手动构建批量插入件:

像这样的 POC:

$ Query = "插入 [dbo].[属性] ([名称],[PetName]) 值"

($ Alot = 0; $10-le 10; $ alot) {
$ I = 65; $ i-le 85; $ i) {
$ Query = "(" [char] $ i "," [char] $ i ")";
如果 ($ i-ne 85-或 $ alot-ne 10) {$ query = ",";}
}
}

构建批处理后,使用现有代码将其传递给 SQL 进行插入。

Buld insert 看起来如下所示:

插入 [dbo]。 [属性] ([名称) 、 [PetName]) 值 ('a' 、 'a') 、 ('b' 、 'b') 、 'e') 、 ('f' 、 'f') 、 ('g' 、 'g') 、 ('h' 、 'h') 、 ('我' 、 ('l' 、 'l') 、 ('m' 、 'm') 、 ('n' 、 'o') 、 ('p' 、 'p') 、 “r”) 、 (“s” 、 “s”)

仅此一项就可以加快插入速度一吨!

  1. 除非你有 25 个逻辑内核,否则不要使用前面提到的 50 个线程。您将花费大部分 SQL insert 时间等待网络,硬盘而不是 CPU。通过让许多线程排队,您将在等待堆栈的较慢部分时保留大部分 CPU 时间。

我想这两件事可以让你的插入时间缩短到几分钟 (我在大约 90 秒内使用了一次 80 k)。

最后一部分可能是重构,这样每个核心都有自己的 Sql 连接,然后在准备好处理所有线程之前,将它保持打开状态。

第6个答案

我对 powershell 了解不多,但是我在工作中一直在 C # 中执行 SQL。

C # 的新异步/等待关键字使得你谈论的事情变得非常容易。 C # 还将为你制作一个线程池,为你的机器提供最佳的线程量。

异步任务执行查询异步 (查询)
{
运行 (() => 执行查询同步 (查询));
}

异步任务执行 ()
{
IList> 查询任务 = 新列表> ();

每个查询
{
查询任务。添加 (执行查询异步 (查询));
}

查询任务中的任务
{
等待任务;
}
}

上面的代码会将所有查询添加到线程池的工作队列中。 然后在完成之前等待他们。结果是,您的 SQL 将达到最大并行级别。

希望这有帮助!

相关问题

如何防止 PHP 中的 SQL 注入? 何时在 MySQL 中使用单引号、双引号和回刻度 让父母和孩子的树文件夹结构在我的 sql< 8 and no CTEs 8="" and="" no=""></ 8 and no CTEs> 如何在异步和线程中执行大量 sql 查询 如何在 Java 8 中创建一个阻塞的背景加载器? 在 cppreference 中,宽松排序的解释是错误的吗? 在 PowerShell 中处理大型数组 。PowerShell 中的 NET 跟踪,无需创建。配置文件 如何从异步调用返回响应? 如何从异步调用返回响应? 为什么在函数内部修改变量后, 它没有改变?-异步代码引用 有没有理由不使用 Java 8 的并行排序? 将 Python 函数应用于熊猫分组数据帧-什么是最有效的方法来加快计算?