SQL Server - Asynchronous Query Execution

How to execute large amount of sql queries asynchronous and in threads

RunspacePool is the way to go here, try this:

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

Unlike powershell jobs, a RunspacePools can share resources. So there is one concurrent queue of all the queries, and each thread keeps its own connection to the database.

As others have said though - unless you're stress testing your database, you're probably better off reorganising the queries into bulk inserts.

Unable to execute sql query in async

If you want to use FOR JSON PATH to maintain full control over the format of the JSON output , you could add it behind the sql query and deserialize json , the following is a example I tested

public class ResponseBody
    {
        public string StudioName { get; set; }
        public string CityName { get; set; }
        public string Street { get; set; }
    }

    public async Task<IActionResult> AsyncSBSystemApps()
    {
        using (var scope = _scopeFactory.CreateScope())
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(@"SELECT 
                    s.Name as StudioName,

                    c.Name as CityName,
                    a.Street as Street
                FROM
                    Studios as s
                INNER JOIN
                    Cities as c ON s.Id = c.StudioId
                INNER JOIN
                    Addresses as a ON c.Id = a.CityId
                where s.Name = 'Studio1' and c.Name = 'Wuxi' and a.Number = '101'
                For JSON PATH  ", connection);

                await connection.OpenAsync();

                var result = command.ExecuteScalar().ToString();

                 // Deserializing json data to object 
                var getModel = JsonConvert.DeserializeObject<List<ResponseBody>>(result);

                //return ...
            }
        }

    }

As mason suggested , you could also use Dapper(Install Dapper package
) to get the query easily like below :

using (SqlConnection connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();

                var queryResult = connection.Query<ResponseBody>(@"SELECT 
                    s.Name as StudioName,

                    c.Name as CityName,
                    a.Street as Street
                FROM
                    Studios as s
                INNER JOIN
                    Cities as c ON s.Id = c.StudioId
                INNER JOIN
                    Addresses as a ON c.Id = a.CityId
                where s.Name = 'Studio1' and c.Name = 'Wuxi' and a.Number = '101'");

                return queryResult ;
            }

Is it possible to asynchronously query SQL Server from Python (3.4)?

The one option I have found so far is the commercial product mxODBC Connect, which unfortunately costs several hundred dollars. By providing a proxy server to SQL Server, it is able to support asynchronous calls. Supposedly the async support is implemented via gevent, so I don't know how well it'll fit into asyncio. From Python's ODBC wiki, mxODBC is the only product listed with async support.

Asynchronous SQL procedure execution set and wait for completion

Good day Shiv,

There are several ways (like always) that you can use in order to implement this requirement. One of these is using this logic:

(1) Create two queues: one will be the trigger to execute the main SP that you want execute in Asynchronous, and the other will be the trigger to execute whatever you want to execute after all the executions ended.

(2) When you create the message in the first queue you should also create a message in the second queue, which will only tell us which execution did not ended yet (first queue gives the information which execution started since once we START the execution we use the message and remove it from the queue).

(3) Inside the SP that you execute using the main first queue (this part executed in synchronous):

(3.1) execute the queries you need

(3.2) clear the equivalent message from the second queue (meaning that this message will removed only after the queries ended)

(3.3) check if there are messages in the second queue. If there are no messages then all the tasks ended and you can execute your final step

** Theoretically instead of using the second queue, you can store data in a table, but using second queue should probably give better performance then updating table each time an execution ended. Anyhow, you test the option of using a table as well.

---

Related Topics