Cómo ejecutar una gran cantidad de consultas sql asincrónicas y en subprocesos

multithreading powershell

Question

Problema: Tengo una gran cantidad de consultas sql (alrededor de 10k-20k) y quiero ejecutarlas de forma asíncrona en 50 (o más) hilos.

Escribí un script de PowerShell para este trabajo, pero es muy lento (tomó alrededor de 20 horas ejecutarlo todo). El resultado deseado es de 3-4 horas como máximo.

Pregunta: ¿Cómo puedo optimizar este script de PowerShell? ¿Debo reconsiderar y usar otra tecnología como python o c# ?

Creo que es un problema de PowerShell, porque cuando whoisactive con whoisactive las consultas se ejecutan rápidamente. Crear, salir y descargar trabajos lleva mucho tiempo, porque para cada hilo se crean instancias PS separadas.

Mi código:

$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 *

}

ACTUALIZACIÓN

Recursos: el servidor DB está en una máquina remota con:

• 24 GB de RAM
• 8 núcleos,
• 500 GB de almacenamiento,
• SQL Server 2016

Queremos utilizar la máxima potencia de la CPU.

Limitación del marco: la única limitación es no usar SQL Server para ejecutar las consultas. Las solicitudes deben provenir de fuentes externas como: Powershell, C #, Python, etc.

Answer 1

1. Agrupe sus consultas según la tabla y las operaciones en esa tabla. Utilizando esto, puede identificar cuántas consultas sql asíncronas podría ejecutar en sus diferentes tablas.
2. Asegúrese de que el tamaño de cada tabla contra la que va a correr. Porque si la tabla contiene millones de filas y si realiza una operación de unión con alguna otra tabla también aumentará el tiempo o si es una operación CUD, entonces también podría bloquear su tabla.
   3. Y también elija el número de subprocesos en función de los núcleos de su CPU y no en función de suposiciones. Debido a que el núcleo de la CPU ejecutará un proceso a la vez, es mejor que pueda crear varios núcleos * 2 hilos son uno eficiente.

Entonces, primero estudie su conjunto de datos y luego haga los 2 elementos anteriores para que pueda identificar fácilmente cuáles son todas las consultas que se ejecutan de manera paralela y eficiente.

Espero que esto te de algunas ideas. Mejor podría usar cualquier script de Python para eso Para que pueda desencadenar fácilmente más de un proceso y también monitorear sus actividades.

Answer 2

Debe reorganizar su secuencia de comandos para mantener abierta una conexión de base de datos en cada subproceso de trabajo, utilizándola para todas las consultas realizadas por ese subproceso. En este momento está abriendo una nueva conexión de base de datos para cada consulta, que agrega una gran cantidad de sobrecarga. Eliminar esa sobrecarga debería acelerar las cosas hasta o más allá de tu objetivo.

Answer 3

Intenta usar SqlCmd .

Puede usar ejecutar múltiples procesos usando Process.Start() y usar sqlcmd para ejecutar consultas en procesos paralelos.

Por supuesto, si está obligado a hacerlo en hilos, esta respuesta ya no será la solución.

Answer 4

Lamentablemente, no tengo el tiempo correcto en este instante para responder esto completamente, pero esto debería ayudar:

Primero, no va a utilizar toda la CPU para insertar tantos registros, casi prometido. ¡Pero!

Como parece que está utilizando comandos de cadena SQL:

1. Divida los insertos en grupos de digamos ~ 100 - ~ 1000 y construya manualmente insertos masivos:

Algo como esto como 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 += ",";}
      }
   }

Una vez que se construye un lote, páselo a SQL para la inserción, usando efectivamente su código existente.

El buld insert se vería algo así como:

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'')

¡Esto solo debería acelerar tus insertos por una tonelada!

2. No use 50 hilos, como se mencionó anteriormente, a menos que tenga más de 25 núcleos lógicos. Pasará la mayor parte del tiempo de inserción de SQL esperando en la red y los discos duros NO en la CPU. Al tener tantos hilos en cola, tendrá la mayor parte de su tiempo de CPU reservado para esperar las partes más lentas de la pila.

Solo estas dos cosas que imagino pueden reducir sus inserciones en cuestión de minutos (hice 80k + una vez usando básicamente este enfoque en aproximadamente 90 segundos).

La última parte podría ser la refactorización para que cada núcleo tenga su propia conexión SQL, y luego la deje abierta hasta que esté listo para deshacerse de todos los hilos.

Answer 5

No sé mucho sobre powershell, pero ejecuto SQL en C # todo el tiempo en el trabajo.

Las nuevas palabras clave asíncronas / en espera de C # hacen que sea extremadamente fácil hacer lo que está hablando. C # también creará un grupo de subprocesos con la cantidad óptima de subprocesos para su máquina.

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;
    }
}

El código anterior agregará todas las consultas a la cola de trabajo del grupo de subprocesos. Luego espere a todos antes de completar. El resultado es que se alcanzará el nivel máximo de paralelismo para su SQL.

¡Espero que esto ayude!

Answer 6

RunspacePool es el camino a seguir aquí, intente esto:

$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
    }
}

A diferencia de los trabajos de PowerShell, un RunspacePools puede compartir recursos. Por lo tanto, hay una cola simultánea de todas las consultas, y cada subproceso mantiene su propia conexión a la base de datos.

Sin embargo, como han dicho otros, a menos que esté haciendo una prueba de esfuerzo de su base de datos, probablemente sea mejor reorganizar las consultas en inserciones masivas.