Optimal way for API call, loop through results and insert into SQL?

Question

I'm wondering what the optimal (speed-wise) way is to accomplish this? This is the current script I have going, but it takes hours, probably because of the nested FOR loop, and start-sleep for API request limitations. Suggestions or changes? I'm all ears on how to improve this.

$token = Invoke-RestMethod -Uri https://<uri path> -Body $body -Method Post -UseBasicParsing

$header = @{Authorization='Bearer '+$token.access_token}

$dataGroup1 = Invoke-RestMethod -Header $header -Uri https://<pathname> -UseBasicParsing

$add = For ($i=0;$i -le $dataGroup1 .data.Count-1; $i++) { $prodDate = (Get-    Date).AddDays(-3).ToString("yyyy-MM-dd")

$header = @{Authorization='Bearer '+$token.access_token}

$uri = "<url path>" + $dataGroup1.data[$i].id + "/readings?" + $prodDate

$readings = Invoke-RestMethod -Header $header -Uri $uri -UseBasicParsing

For ($f=0;$f -le $readings.data.Count-1; $f++)
{
 If ($($readings.data[$f].id) -gt 0)
  {
   "INSERT INTO dbo.Table1       VALUES('$($readings.data[$f].id)'
,'$($dataGroup1.data[$i].id)'
,convert(date,dateadd(S,$($readings.data[$f].reading_time),'1970-01-01'))
,'$($readings.data[$f].Data1)'
,'$($readings.data[$f].Data2)'
,'$($readings.data[$f].Data3)'
,'$($readings.data[$f].Data4)'
,'$($readings.data[$f].Data5)'
,'$($readings.data[$f].Data6)'
,'$($readings.data[$f].Data7)'
,'$($readings.data[$f].Data8)'
,convert(date,dateadd(S,$($readings.data[$f].Data9), '1970-01-01'))
,'$($readings.data[$f].Data10)'
,GETDATE())"
} Start-Sleep -Seconds 3
}
}

Use the migthy [PowerShell Pipeline](https://devblogs.microsoft.com/powershell-community/mastering-the-steppable-pipeline/) and *stream*: `$dataGroups | Invoke-RestMethod .... | Invoke-SqlCmd ...` — iRon, Aug 03 '23 at 05:39

score -2 · Answer 1 · answered Aug 03 '23 at 03:22

You can try to extract your insert operation into a function and use runspace.

More explanation on runspace here : https://livebook.manning.com/concept/powershell/runspace#:~:text=A%20runspace%20is%20a%20container,a%20%5BPowerShell%5D%20object%20invocation.

# Define the function that inserts data into the database
function Insert-Data {
    param($reading)

    # Construct the INSERT SQL statement
    $insertSQL = @"
    INSERT INTO dbo.Table1 VALUES ('$($reading.id)'
    ,'$($dataGroup1.data[$i].id)'
    ,convert(date,dateadd(S,$($reading.reading_time),'1970-01-01'))
    ,'$($reading.Data1)'
    ,'$($reading.Data2)'
    ,'$($reading.Data3)'
    ,'$($reading.Data4)'
    ,'$($reading.Data5)'
    ,'$($reading.Data6)'
    ,'$($reading.Data7)'
    ,'$($reading.Data8)'
    ,convert(date,dateadd(S,$($reading.Data9), '1970-01-01'))
    ,'$($reading.Data10)'
    ,GETDATE())
"@

    # Execute the INSERT statement (replace the following line with your database insert code)
    # Invoke-SqlCmd -ServerInstance "YourServer" -Database "YourDatabase" -Query $insertSQL
    Write-Host $insertSQL
}

$readings = Invoke-RestMethod -Header $header -Uri $uri -UseBasicParsing

# Change this based on the max-threads you feel sufficient to ur system
$maxThreads = 5

# Create a thread-safe collection to hold the readings
$readingQueue = [System.Collections.Concurrent.ConcurrentQueue[PSObject]]::new()

# Here you add your reading into Thread-Safe Queue
foreach ($reading in $readings.data) {
    $readingQueue.Enqueue($reading)
}

# Define the scriptblock that will be run in parallel
$runspaceScriptBlock = {
    param($readingQueue)

    while ($readingQueue.TryDequeue([ref]$reading)) {
        Insert-Data $reading
    }
}

# Define run space
$runspaces = @()
for ($i = 0; $i -lt $maxThreads; $i++) {
    $runspace = [powershell]::Create()
    $runspace.AddScript($runspaceScriptBlock).AddArgument($readingQueue)
    $runspaces += $runspace
    $runspace.BeginInvoke()
}

# Wait for all to complete
$runspaces | ForEach-Object { $_.EndInvoke() }

If you go into parallel treads, you better use the latest version of PowerShell with [`Foreach-Object -Parallel`](https://devblogs.microsoft.com/powershell/powershell-foreach-object-parallel-feature/) or if it concerns *Windows* PowerShell use: [`Invoke-Parallel`](https://stackoverflow.com/a/74257557/1701026). As an aside: [using the increase assignment operator (`+=`) to create a collection as in `$runspaces += $runspace` is *not optimal*](https://stackoverflow.com/a/60708579/1701026), besides it looks to me that any thread above the `$MaxThreads` is just skipped. — iRon, Aug 03 '23 at 09:57

Optimal way for API call, loop through results and insert into SQL?

1 Answers1