1

I have an array of objects, from which, I need to build multiple SQL data tables.

A sample of the devices (many properties ommited) looks like this:

$allDevices = @(
    [PSCustomObject]@{
        id               = 18
        name             = 'server1'
        systemProperties = [PSCustomObject]@{name='system.totalphysicalmemory'; value='14.00GB'}, @{name='system.systemtype'; value='x64-based PC'},@{name='system.domain'; value='domain.local'}
    }
    [PSCustomObject]@{
        id               = 32
        name             = 'server2'
        systemProperties = [PSCustomObject]@{name = 'system.totalphysicalmemory'; value = '8.00GB' }, @{name = 'system.systemtype'; value = 'x64-based PC' }, @{name = 'system.domain'; value = 'domain.local' }
    }
)

My datatable definitions look like this:

$devicePropertyDataTable = New-Object Data.datatable
$null = $devicePropertyDataTable.Columns.Add('deviceRecordId')
$null = $devicePropertyDataTable.Columns.Add('devicePropRecordId')
$null = $devicePropertyDataTable.Columns.Add('devicePropPropertyName')
$null = $devicePropertyDataTable.Columns.Add('devicePropPropertyValue')

$deviceCustomPropDataTable = New-Object Data.datatable
$null = $deviceCustomPropDataTable.Columns.Add('deviceRecordId')
$null = $deviceCustomPropDataTable.Columns.Add('deviceCustomPropRecordId')
$null = $deviceCustomPropDataTable.Columns.Add('deviceCustomPropPropertyName')
$null = $deviceCustomPropDataTable.Columns.Add('deviceCustomPropPropertyValue')

$deviceSystemPropDataTable = New-Object Data.datatable
$null = $deviceSystemPropDataTable.Columns.Add('deviceRecordId')
$null = $deviceSystemPropDataTable.Columns.Add('deviceSystemPropRecordId')
$null = $deviceSystemPropDataTable.Columns.Add('deviceSystemPropPropertyName')
$null = $deviceSystemPropDataTable.Columns.Add('deviceSystemPropPropertyValue')

$deviceAutoPropDataTable = New-Object Data.datatable
$null = $deviceAutoPropDataTable.Columns.Add('deviceRecordId')
$null = $deviceAutoPropDataTable.Columns.Add('deviceAutoPropRecordId')
$null = $deviceAutoPropDataTable.Columns.Add('deviceAutoPropPropertyName')
$null = $deviceAutoPropDataTable.Columns.Add('deviceAutoPropPropertyValue')

$deviceInheritPropDataTable = New-Object Data.datatable
$null = $deviceInheritPropDataTable.Columns.Add('deviceRecordId')
$null = $deviceInheritPropDataTable.Columns.Add('deviceInheritPropRecordId')
$null = $deviceInheritPropDataTable.Columns.Add('deviceInheritPropPropertyName')
$null = $deviceInheritPropDataTable.Columns.Add('deviceInheritPropPropertyValue')

I am running the following foreach loop, to add rows to each of the datatables.

Foreach ($device in $allDevices) {
    $deviceRecordId = ($sqlResponse | Where-Object { $_.deviceId -eq $device.id } | Select-Object -Last 1).deviceRecordId

    #region deviceProperty table
    $device.PsObject.Properties | Where-Object { ($_.MemberType -eq 'NoteProperty') } | ForEach-Object {
        $dr = $devicePropertyDataTable.NewRow()
        $dr.Item('deviceRecordId') = $deviceRecordId
        $dr.Item('devicePropPropertyName') = $_.Name
        $dr.Item('devicePropPropertyValue') = ($_.Value | Out-String)
        $devicePropertyDataTable.Rows.Add($dr)
    }
    #endregion deviceProperty table

    #region deviceCustomProperty table
    If ($device.customProperties) {
        $device.customProperties | ForEach-Object {
            $dr = $deviceCustomPropDataTable.NewRow()
            $dr.Item('deviceRecordId') = $deviceRecordId
            $dr.Item('deviceCustomPropPropertyName') = $_.Name
            $dr.Item('deviceCustomPropPropertyValue') = ($_.Value | Out-String)
            $deviceCustomPropDataTable.Rows.Add($dr)
        }
    }
    #endregion deviceCustomProperty table

    #region deviceSystemProperty datatable
    $device.systemProperties | ForEach-Object {
        $dr = $deviceSystemPropDataTable.NewRow()
        $dr.Item('deviceRecordId') = $deviceRecordId
        $dr.Item('deviceSystemPropPropertyName') = $_.Name
        $dr.Item('deviceSystemPropPropertyValue') = ($_.Value | Out-String)
        $deviceSystemPropDataTable.Rows.Add($dr)
    }
    #endregion deviceSystemProperty datatable

    #region deviceAutoProperty datatable
    $device.autoProperties | ForEach-Object {
        $dr = $deviceAutoPropDataTable.NewRow()
        $dr.Item('deviceRecordId') = $deviceRecordId
        $dr.Item('deviceAutoPropPropertyName') = $_.Name
        $dr.Item('deviceAutoPropPropertyValue') = ($_.Value | Out-String)
        $deviceAutoPropDataTable.Rows.Add($dr)
    }
    #endregion deviceAutoProperty datatable

    #region deviceInheritProperty datatable
    $device.autoProperties | ForEach-Object {
        $dr = $deviceInheritPropDataTable.NewRow()
        $dr.Item('deviceRecordId') = $deviceRecordId
        $dr.Item('deviceInheritPropPropertyName') = $_.Name
        $dr.Item('deviceInheritPropPropertyValue') = ($_.Value | Out-String)
        $deviceInheritPropDataTable.Rows.Add($dr)
    }
    #endregion deviceInheritProperty datatable
}

This is taking 3ish seconds (as far as I can tell) for most of the devices. With 24000 devices, that is 20+ hours to run the script, so I am looking for ways to optimize. I thought about using Foreach-Object -Parallel:

$allDevices | Foreach-Object -Parallel {
    $device = $_

    $deviceRecordId = ($using:deviceResponse | Where-Object { $_.deviceId -eq $device.id } | Select-Object -Last 1).deviceRecordId

    #region deviceProperty table
    $device.PsObject.Properties | Where-Object { ($_.MemberType -eq 'NoteProperty') } | ForEach-Object {
        $dr = $devicePropertyDataTable.NewRow()
        $dr.Item('deviceRecordId') = $deviceRecordId
        $dr.Item('devicePropPropertyName') = $_.Name
        $dr.Item('devicePropPropertyValue') = ($_.Value | Out-String)
        $devicePropertyDataTable.Rows.Add($dr)
    }
    #endregion deviceProperty table

    #region deviceCustomProperty table
    If ($device.customProperties) {
        $device.customProperties | ForEach-Object {
            $dr = $deviceCustomPropDataTable.NewRow()
            $dr.Item('deviceRecordId') = $deviceRecordId
            $dr.Item('deviceCustomPropPropertyName') = $_.Name
            $dr.Item('deviceCustomPropPropertyValue') = ($_.Value | Out-String)
            $deviceCustomPropDataTable.Rows.Add($dr)
        }
    }
    #endregion deviceCustomProperty table

    #region deviceSystemProperty datatable
    $device.systemProperties | ForEach-Object {
        $dr = $deviceSystemPropDataTable.NewRow()
        $dr.Item('deviceRecordId') = $deviceRecordId
        $dr.Item('deviceSystemPropPropertyName') = $_.Name
        $dr.Item('deviceSystemPropPropertyValue') = ($_.Value | Out-String)
        $deviceSystemPropDataTable.Rows.Add($dr)
    }
    #endregion deviceSystemProperty datatable

    #region deviceAutoProperty datatable
    $device.autoProperties | ForEach-Object {
        $dr = $deviceAutoPropDataTable.NewRow()
        $dr.Item('deviceRecordId') = $deviceRecordId
        $dr.Item('deviceAutoPropPropertyName') = $_.Name
        $dr.Item('deviceAutoPropPropertyValue') = ($_.Value | Out-String)
        $deviceAutoPropDataTable.Rows.Add($dr)
    }
    #endregion deviceAutoProperty datatable

    #region deviceInheritProperty datatable
    $device.autoProperties | ForEach-Object {
        $dr = $deviceInheritPropDataTable.NewRow()
        $dr.Item('deviceRecordId') = $deviceRecordId
        $dr.Item('deviceInheritPropPropertyName') = $_.Name
        $dr.Item('deviceInheritPropPropertyValue') = ($_.Value | Out-String)
        $deviceInheritPropDataTable.Rows.Add($dr)
    }
    #endregion deviceInheritProperty datatable
}

The problem is that I cannot add data to the data table rows inside the -Parallel loop.

InvalidOperation:
Line |
  26 |          $dr = $devicePropertyDataTable.NewRow()
     |          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | You cannot call a method on a null-valued expression.

Is there a way around this, or is anyone aware of any other ways to reduce the amount of time it takes to populate the data tables?

StackExchangeGuy
  • 741
  • 16
  • 36

0 Answers0