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

by

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 
}

  • 3

    Why are you not using INNER JOIN ?

    – 

  • 3

    Iterating row by row is always going to be slow, you should be treating the result (having used @SantiagoSquarzon’s suggestion to write a single query) as a single dataset and writing it to a file in one go

    – 

  • 2

    try this query: gist.github.com/santisq/… if it works it should be way faster than what you’re doing

    – 

  • 6

    I’m surprised you can connect to SQL Server 2005 from PoSh 7; 2005 ran completely out of support almost a decade ago and doesn’t support modern TLS. PoSh 7 on the other hand can only be installed on environments that normally require TLS1.2+. In truth, you really need to get that instance upgraded; the longer you leave it the harder it is; it’s already difficult with 2008 and 2012 being unsupported, and 2014 is on the edge (and I’m not sure you can restore 2005 databases on 2014 so you’re honestly in a very poor position).

    – 

  • Apparently you missed quite some “PowerShell Performance” documentation and other posts, as e.g.: PowerShell scripting performance considerations, Why should I avoid using the increase assignment operator (+=) to create a collection and in case of a local (PowerShell) join: what’s the best way to join two tables into one?.

    – 

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

Leave a Comment