大量の SQL クエリを非同期およびスレッドで実行する方法




multithreading powershell (4)

問題: 大量のSQLクエリ(約10k〜20k)があり、50(またはそれ以上)のスレッドで非同期に実行したい。

このジョブ用のPowerShellスクリプトを作成しましたが、非常に遅いです(すべてを実行するのに約20時間かかりました)。 望ましい結果は最大3〜4時間です。

質問: このPowerShellスクリプトを最適化するにはどうすればよいですか? pythonc# などの別のテクノロジーを再検討して使用する必要がありますか?

whoisactiveで確認すると、クエリが高速に実行されているため、これはPowerShellの問題だと思います。 スレッドごとに個別のPSインスタンスが作成されるため、ジョブの作成、終了、およびアンロードには時間がかかります。

私のコード:

$NumberOfParallerThreads = 50;


$Arr_AllQueries = @('Exec [mystoredproc] @param1=1, @param2=2',
                    'Exec [mystoredproc] @param1=11, @param2=22',
                    'Exec [mystoredproc] @param1=111, @param2=222')

#Creating the batches
$counter = [pscustomobject] @{ Value = 0 };
$Batches_AllQueries = $Arr_AllQueries | Group-Object -Property { 
    [math]::Floor($counter.Value++ / $NumberOfParallerThreads) 
};

forEach ($item in $Batches_AllQueries) {
    $tmpBatch = $item.Group;

    $tmpBatch | % {

        $ScriptBlock = {
            # accept the loop variable across the job-context barrier
            param($query) 
            # Execute a command

            Try 
            {
                Write-Host "[processing '$query']"
                $objConnection = New-Object System.Data.SqlClient.SqlConnection;
                $objConnection.ConnectionString = 'Data Source=...';

                $ObjCmd = New-Object System.Data.SqlClient.SqlCommand;
                $ObjCmd.CommandText = $query;
                $ObjCmd.Connection = $objConnection;
                $ObjCmd.CommandTimeout = 0;

                $objAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
                $objAdapter.SelectCommand = $ObjCmd;
                $objDataTable = New-Object System.Data.DataTable;
                $objAdapter.Fill($objDataTable)  | Out-Null;

                $objConnection.Close();
                $objConnection = $null;
            } 
            Catch 
            { 
                $ErrorMessage = $_.Exception.Message
                $FailedItem = $_.Exception.ItemName
                Write-Host "[Error processing: $($query)]" -BackgroundColor Red;
                Write-Host $ErrorMessage 
            }

        }

        # pass the loop variable across the job-context barrier
        Start-Job $ScriptBlock -ArgumentList $_ | Out-Null
    }

    # Wait for all to complete
    While (Get-Job -State "Running") { Start-Sleep 2 }

    # Display output from all jobs
    Get-Job | Receive-Job | Out-Null

    # Cleanup
    Remove-Job *

}

更新

リソース: DBサーバーは、次の機能を備えたリモートマシン上にあります。

  • 24GB RAM、
  • 8コア、
  • 500GBストレージ、
  • SQL Server 2016

最大のCPUパワーを使用します。

フレームワークの制限: 唯一の制限は、SQL Serverを使用してクエリを実行し ない ことです。 要求は、Powershell、C#、Pythonなどの外部ソースから送信する必要があります。


  1. テーブルとそのテーブルの操作に基づいてクエリをグループ化します。 これを使用して、異なるテーブルに対して実行できる非同期SQLクエリの量を特定できます。
  2. 実行する各テーブルのサイズを確認してください。 テーブルに数百万の行が含まれており、他のテーブルと結合操作を行うと時間が長くなる場合、またはCUD操作である場合、テーブルもロックされる可能性があるためです。
    1. また、仮定ではなくCPUコアに基づいてスレッド数を選択します。 CPUコアは一度に1つのプロセスを実行するため、 複数のコアを 作成できます * 2つの スレッドが効率的なスレッドです。

したがって、最初にデータセットを調べてから、上記の2つの項目を実行して、すべてのクエリが並列かつ効率的に実行されるものを簡単に識別できるようにします。

これがいくつかのアイデアを与えることを願っています。 そのために任意のpythonスクリプトを使用できれば、複数のプロセスを簡単にトリガーしたり、それらのアクティビティを監視したりできます。


RunspacePoolはここに行く方法です、これを試してください:

$AllQueries = @( ... )
$MaxThreads = 5

# Each thread keeps its own connection but shares the query queue
$ScriptBlock = {
    Param($WorkQueue)

    $objConnection = New-Object System.Data.SqlClient.SqlConnection
    $objConnection.ConnectionString = 'Data Source=...'

    $objCmd = New-Object System.Data.SqlClient.SqlCommand
    $objCmd.Connection = $objConnection
    $objCmd.CommandTimeout = 0

    $query = ""

    while ($WorkQueue.TryDequeue([ref]$query)) {
        $objCmd.CommandText = $query
        $objAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $objCmd
        $objDataTable = New-Object System.Data.DataTable
        $objAdapter.Fill($objDataTable) | Out-Null
    }

    $objConnection.Close()

}

# create a pool
$pool = [RunspaceFactory]::CreateRunspacePool(1, $MaxThreads)
$pool.ApartmentState  = 'STA'
$pool.Open()

# convert the query array into a concurrent queue
$workQueue = New-Object System.Collections.Concurrent.ConcurrentQueue[object]
$AllQueries | % { $workQueue.Enqueue($_) }

$threads = @()

# Create each powershell thread and add them to the pool
1..$MaxThreads | % {
    $ps = [powershell]::Create()
    $ps.RunspacePool = $pool
    $ps.AddScript($ScriptBlock) | Out-Null
    $ps.AddParameter('WorkQueue', $workQueue) | Out-Null
    $threads += [pscustomobject]@{
        Ps = $ps
        Handle = $null
    }
}

# Start all the threads
$threads | % { $_.Handle = $_.Ps.BeginInvoke() }

# Wait for all the threads to complete - errors will still set the IsCompleted flag
while ($threads | ? { !$_.Handle.IsCompleted }) {
    Start-Sleep -Seconds 1
}

# Get any results and display an errors
$threads | % {
    $_.Ps.EndInvoke($_.Handle) | Write-Output
    if ($_.Ps.HadErrors) {
        $_.Ps.Streams.Error.ReadAll() | Write-Error
    }
}

PowerShellジョブとは異なり、RunspacePoolsはリソースを共有できます。 したがって、すべてのクエリの同時キューが1つあり、各スレッドはデータベースへの独自の接続を保持します。

ただし、他の人が言っているように、データベースのストレステストを行っている場合を除き、クエリを一括挿入に再編成することをお勧めします。


残念ながら、この瞬間にこれに完全に答える時間はありませんが、これは役立つはずです:

まず、ほぼすべてのレコードを挿入するためにCPU全体を使用するわけではありません。 しかし!

表示されるので、SQL文字列コマンドを使用しています。

  1. 挿入をたとえば〜100〜〜1000のグループに分割し、手動で一括挿入を作成します。

POCとしてのこのようなもの:

  $query = "INSERT INTO [dbo].[Attributes] ([Name],[PetName]) VALUES "

  for ($alot = 0; $alot -le 10; $alot++){
     for ($i = 65; $i -le 85; $i++) {
       $query += "('" + [char]$i + "', '" + [char]$i + "')"; 
       if ($i -ne 85 -or $alot -ne 10) {$query += ",";}
      }
   }

バッチが構築されたら、既存のコードを効果的に使用して、挿入のためにSQLに渡します。

buld insertは次のようになります。

INSERT INTO [dbo].[Attributes] ([Name],[PetName]) VALUES ('A', 'A'),('B', 'B'),('C', 'C'),('D', 'D'),('E', 'E'),('F', 'F'),('G', 'G'),('H', 'H'),('I', 'I'),('J', 'J'),('K', 'K'),('L', 'L'),('M', 'M'),('N', 'N'),('O', 'O'),('P', 'P'),('Q', 'Q'),('R', 'R'),('S', 'S')

これだけでも、挿入が1トン速くなります!

  1. 25個以上の論理コアがない限り、前述のように50個のスレッドを使用しないでください。 SQL挿入時間のほとんどをネットワークで待機し、CPUではなくハードドライブを使用します。 多数のスレッドをキューに入れることにより、CPU時間のほとんどを、スタックの遅い部分を待機するために予約できます。

これらの2つのことだけで、数分で挿入を完了することができると思います(基本的にこのアプローチを約90秒で80k +使用しました)。

最後の部分はリファクタリングで、各コアが独自のSql接続を取得し、すべてのスレッドを破棄する準備ができるまで開いたままにします。


私はpowershellについてあまり知りませんが、仕事中は常にC#でSQLを実行します。

C#の新しいasync / awaitキーワードを使用すると、話していることを非常に簡単に実行できます。 C#は、マシンに最適な量のスレッドでスレッドプールも作成します。

async Task<DataTable> ExecuteQueryAsync(query)
{
    return await Task.Run(() => ExecuteQuerySync(query));
}

async Task ExecuteAllQueriesAsync()
{
    IList<Task<DataTable>> queryTasks = new List<Task<DataTable>>();

    foreach query
    {
         queryTasks.Add(ExecuteQueryAsync(query));
    }

    foreach task in queryTasks
    {
         await task;
    }
}

上記のコードは、すべてのクエリをスレッドプールの作業キューに追加します。 次に、それらをすべて待ってから完了します。 その結果、SQLの並列処理の最大レベルに到達します。

お役に立てれば!







parallel-processing