What is the fastest way to extract data from SQL Server 2005 using PowerShell 7?

Question

This is not just a simple extraction. I have two tables. I take the values from one table and then use one column from this table to find a corresponding value in another table and then extract all the values. Details: IN the view vEngBillsOfMaterial_Aras there is column usedonRn. I get all the values corresponding to usedOnRn from another view [vEngParts_Master_Aras] Once the values are received I combined the values received and existing values from vEngBillsOfMaterial_Aras in an array because this what the end result should be.

I am using Powershell 7 to get\process the data from SQL Server 2005

There are couple of ways I could do this. Keep updating the array as the rows are process and write to csv file at once or write each row to csv as it is processed( this is the method in the code below)

The challenge is there are 700,000 rows to be processed and writing it to CSV is an extremely slow process. We are talking 12hrs to write 50,000 rows. I have tried creating a view instead of CSV and update the view but the view cannot be update because the data contain "derived" or "constant" values.

What is the fastest way to achieve the desired results here.

$server = "ServerName"
$database = "DBName"
$csvFilePath = "CSVPath"
$query = "SELECT Distinct UsedOnRn From [Eng].[dbo].[vEngBillsOfMaterial] Order By UsedOnRn ASC"
$invokeQuery = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query
$array = @()
foreach($item in $invokeQuery){
    $usedOnRn = $item.UsedOnRn
    $queryParts = "Select Revision,PartRn,Part_Number from [Eng].[dbo].[vEngParts_Master] Where PartRn = '$usedOnRn'"
    $queryUsedOnRn = "SELECT * From [Eng].[dbo].[vEngBillsOfMaterial] Where UsedOnRn = '$usedOnRn'"
    $invokeUsedOnRn = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query  $queryUsedOnRn
    $invokeQueryParts = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query  $queryParts
    Foreach($row in $invokeUsedOnRn){
        $array = [PSCustomObject]@{
            PartRn = $row.PartRn
            PART_NUMBER = $row.PART_NUMBER
            Revision =  $row.Revision
            UsedOnRn =$row.UsedOnRn
            RevOfUsedOnRn = $invokeQueryParts.Revision
            PartNoOfUR = $invokeQueryParts.Part_Number
            Item = $row.Item
            QtyPerAssy = $row.QtyPerAssy
            Remarks = $row.Remarks
        }
        $array | Format-table 
    }
    $array | Export-Csv -Path $csvFilePath -Append -NoTypeInformation -Force 
}

Answer 1

Use directly thiw query :

SELECT T1.PartRn,
       T2.Part_Number,
       T2.Revision,
       T2.UsedOnRn,
       RevOfUsedOnRn,
       PartNoOfUR,
       Item,
       QtyPerAssy,
       Remarks
From  Eng.dbo.vEngBillsOfMaterial AS T1
      JOIN Eng.dbo.vEngParts_Master AS T2
         ON t1.PartRn = T2.UsedOnRn