question

ChristopherJack-1763 avatar image
0 Votes"
ChristopherJack-1763 asked RichMatheisen-8856 answered

Is there any way to make this quicker?

Hi,

I have a loop which loops through two arrays two make a header/item file.

 for ($x=0; $x -le $header.count-1; $x++)
 {
     $Headeroutput =  @(); 
     $Headeroutput  += $header[$x]."Record_Type",$header[$x]."OrderNumber",$header[$x]."OrderDate",$header[$x]."delivery_contact_name",$header[$x]."Company_Name",$header[$x]."delivery_addressline1",$header[$x]."delivery_addressline2",$header[$x]."City",$header[$x]."delivery_post_code",$header[$x]."County",$header[$x]."delivery_country_code",$header[$x]."delivery_contact_number",$header[$x]."email",$header[$x]."Currency_code",$header[$x]."Export_AWB",$header[$x]."Export_Carrier_Name",$header[$x]."Duplicate action",$header[$x]."RMA",$header[$x]."Export_Date",$header[$x]."Free Return",$header[$x]."delivery_addressline3",$header[$x]."Suberb",$header[$x]."Neighborhood",$header[$x]."Custom_field_1",$header[$x]."Custom_field_2",$header[$x]."Custom_field_3",$header[$x]."Custom_field_4",$header[$x]."Custom_field_5"
     $Headeroutput -join "," | Out-File $filename -Append -encoding utf8
     $iteminfo  = @(); 
     $iteminfo = $item | Where-Object {$_."Order_Reference" -eq $header[$x]."OrderNumber" } | Select-Object "Record_Type", "Order_Reference", "SKU_Code", "SKU_Description", "Quantity", "Item_Price", "Unit_Weight", "Weight", "length", "width", "height", "Dimensions_UoM", "HS_Code", "Item_Origin", "ImageURL", "Non_Returnable", "Dangerous_Goods", "Export_Date", "Export_AWB Export_Carrier_Name", "SKU_URL", "Tracking", "Custom_field_1", "Custom_field_2", "Custom_field_3", "Custom_field_4", "Custom_field_5" , "Days for return", "CostPrice"
       
     if($iteminfo.count -eq 1){$test=0}else{$test = $iteminfo.count-1 }
     for($y=0; $y -le $test; $y++)
     {
         $itemoutput = @(); 
         $itemoutput  += $iteminfo[$y]."Record_Type", $iteminfo[$y]."SKU_Code", $iteminfo[$y]."SKU_Description", $iteminfo[$y]."Quantity", $iteminfo[$y]."Item_Price", $iteminfo[$y]."Unit_Weight", $iteminfo[$y]."Weight", $iteminfo[$y]."length", $iteminfo[$y]."width", $iteminfo[$y]."height", $iteminfo[$y]."Dimensions_UoM", $iteminfo[$y]."HS_Code", $iteminfo[$y]."Item_Origin", $iteminfo[$y]."ImageURL", $iteminfo[$y]."Non_Returnable", $iteminfo[$y]."Dangerous_Goods", $iteminfo[$y]."Export_Date", $iteminfo[$y]."Export_AWB", $iteminfo[$y]."Export_Carrier_Name", $iteminfo[$y]."SKU_URL", $iteminfo[$y]."Tracking", $iteminfo[$y]."Custom_field_1", $iteminfo[$y]."Custom_field_2", $iteminfo[$y]."Custom_field_3", $iteminfo[$y]."Custom_field_4", $iteminfo[$y]."Custom_field_5", $iteminfo[$y]."Days for return", $iteminfo[$y]."CostPrice"
         $itemoutput -join "," | Out-File $filename -Append -encoding utf8
     }
 }

This is ok for very small files however it dies when the files are 10mb over.. i presume because it having to search through so much information.

Header can be 22551 rows
and the item 111926 rows.

Is there anyway to make it quicker?

Help appreciated.

windows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichMatheisen-8856 avatar image
1 Vote"
RichMatheisen-8856 answered ChristopherJack-1763 commented

No guaranties, but try this:

 for ($x = 0; $x -le $header.count - 1; $x++) {
     # eliminate the need for repeated subscripting
     $h = $header[$x]
    
     # don't repeatedly expand an array for every value -- that creates a new array and copies the old one
     $h."Record_Type", $h."OrderNumber", $h."OrderDate", $h."delivery_contact_name", $h."Company_Name", $h."delivery_addressline1", $h."delivery_addressline2", $h."City", 
     $h."delivery_post_code", $h."County", $h."delivery_country_code", $h."delivery_contact_number", $h."email", $h."Currency_code", $h."Export_AWB", $h."Export_Carrier_Name", 
     $h."Duplicate action", $h."RMA", $h."Export_Date", $h."Free Return", $h."delivery_addressline3", $h."Suberb", $h."Neighborhood", $h."Custom_field_1", $h."Custom_field_2", 
     $h."Custom_field_3", $h."Custom_field_4", $h."Custom_field_5" -join "," | 
         Out-File $filename -Append -encoding utf8
    
     # if $item is an array, is it ordered by the "Order_Reference" property?
     # if it is you could stop searching after you find the last match -- but you'd have to use something other than "Where-Object"
     # to do the comparison
     $item | 
         Where-Object { $_."Order_Reference" -eq $h."OrderNumber" } | 
             Select-Object "Record_Type", "Order_Reference", "SKU_Code", "SKU_Description", "Quantity", "Item_Price", "Unit_Weight", "Weight", "length", "width", "height", "Dimensions_UoM", "HS_Code", "Item_Origin", "ImageURL", "Non_Returnable", "Dangerous_Goods", "Export_Date", "Export_AWB Export_Carrier_Name", "SKU_URL", "Tracking", "Custom_field_1", "Custom_field_2", "Custom_field_3", "Custom_field_4", "Custom_field_5" , "Days for return", "CostPrice" |
                 ForEach-Object{
                     $_."Record_Type", $_."SKU_Code", $_."SKU_Description", $_."Quantity", $_."Item_Price", $_."Unit_Weight", $_."Weight", $_."length", $_."width", $_."height", $_."Dimensions_UoM", 
                     $_."HS_Code", $_."Item_Origin", $_."ImageURL", $_."Non_Returnable", $_."Dangerous_Goods", $_."Export_Date", $_."Export_AWB", $_."Export_Carrier_Name", $_."SKU_URL", 
                     $_."Tracking", $_."Custom_field_1", $_."Custom_field_2", $_."Custom_field_3", $_."Custom_field_4", $_."Custom_field_5", $_."Days for return", $_."CostPrice" -join "," | 
                         Out-File $filename -Append -encoding utf8
                 }
 }
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Rich, Seems a bit quicker but still takes over an hour to process.

I wonder if it would be quick to fire off a sql request for every order write it out then fire off.

0 Votes 0 ·
IanXue-MSFT avatar image
1 Vote"
IanXue-MSFT answered ChristopherJack-1763 commented

Hi,

Does this work better?

 $result = @()
 for ($x=0; $x -le $header.count-1; $x++)
 {
     $Headeroutput =  @(); 
     $Headeroutput  += $header[$x]."Record_Type",$header[$x]."OrderNumber",$header[$x]."OrderDate",$header[$x]."delivery_contact_name",$header[$x]."Company_Name",$header[$x]."delivery_addressline1",$header[$x]."delivery_addressline2",$header[$x]."City",$header[$x]."delivery_post_code",$header[$x]."County",$header[$x]."delivery_country_code",$header[$x]."delivery_contact_number",$header[$x]."email",$header[$x]."Currency_code",$header[$x]."Export_AWB",$header[$x]."Export_Carrier_Name",$header[$x]."Duplicate action",$header[$x]."RMA",$header[$x]."Export_Date",$header[$x]."Free Return",$header[$x]."delivery_addressline3",$header[$x]."Suberb",$header[$x]."Neighborhood",$header[$x]."Custom_field_1",$header[$x]."Custom_field_2",$header[$x]."Custom_field_3",$header[$x]."Custom_field_4",$header[$x]."Custom_field_5"
     $result += $Headeroutput -join "," 
     $iteminfo  = @(); 
     $iteminfo = $item | Where-Object {$_."Order_Reference" -eq $header[$x]."OrderNumber" } | Select-Object "Record_Type", "Order_Reference", "SKU_Code", "SKU_Description", "Quantity", "Item_Price", "Unit_Weight", "Weight", "length", "width", "height", "Dimensions_UoM", "HS_Code", "Item_Origin", "ImageURL", "Non_Returnable", "Dangerous_Goods", "Export_Date", "Export_AWB Export_Carrier_Name", "SKU_URL", "Tracking", "Custom_field_1", "Custom_field_2", "Custom_field_3", "Custom_field_4", "Custom_field_5" , "Days for return", "CostPrice"
           
     if($iteminfo.count -eq 1){$test=0}else{$test = $iteminfo.count-1 }
     for($y=0; $y -le $test; $y++)
     {
         $itemoutput = @(); 
         $itemoutput  += $iteminfo[$y]."Record_Type", $iteminfo[$y]."SKU_Code", $iteminfo[$y]."SKU_Description", $iteminfo[$y]."Quantity", $iteminfo[$y]."Item_Price", $iteminfo[$y]."Unit_Weight", $iteminfo[$y]."Weight", $iteminfo[$y]."length", $iteminfo[$y]."width", $iteminfo[$y]."height", $iteminfo[$y]."Dimensions_UoM", $iteminfo[$y]."HS_Code", $iteminfo[$y]."Item_Origin", $iteminfo[$y]."ImageURL", $iteminfo[$y]."Non_Returnable", $iteminfo[$y]."Dangerous_Goods", $iteminfo[$y]."Export_Date", $iteminfo[$y]."Export_AWB", $iteminfo[$y]."Export_Carrier_Name", $iteminfo[$y]."SKU_URL", $iteminfo[$y]."Tracking", $iteminfo[$y]."Custom_field_1", $iteminfo[$y]."Custom_field_2", $iteminfo[$y]."Custom_field_3", $iteminfo[$y]."Custom_field_4", $iteminfo[$y]."Custom_field_5", $iteminfo[$y]."Days for return", $iteminfo[$y]."CostPrice"
         $result += $itemoutput -join "," 
     }
 }
 $result | Out-File $filename -encoding utf8

Best Regards,
Ian Xue
============================================
If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Ian, Seems a bit quicker but still takes over an hour to process.

I wonder if it would be quick to fire off a sql request for every order write it out then fire off.

0 Votes 0 ·
RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered

I have no data to test this against, but it should give you an idea of how to do this if it doesn't work exactly the way I think it does. :-)

 # put both arrays in ascending sequence by order number
 $item = $item | Sort-Object Order_Reference
 $header = $header | Sort-Object OrderNumber
 $RememberLastOrderPosition = 0
 for ($x = 0; $x -lt $header.count; $x++) {
     # eliminate the need for repeated subscripting
     $h = $header[$x]
    
     # don't repeatedly expand an array for every value -- that creates a new array and copies the old one
     $h."Record_Type", $h."OrderNumber", $h."OrderDate", $h."delivery_contact_name", $h."Company_Name", $h."delivery_addressline1", $h."delivery_addressline2", $h."City", 
     $h."delivery_post_code", $h."County", $h."delivery_country_code", $h."delivery_contact_number", $h."email", $h."Currency_code", $h."Export_AWB", $h."Export_Carrier_Name", 
     $h."Duplicate action", $h."RMA", $h."Export_Date", $h."Free Return", $h."delivery_addressline3", $h."Suberb", $h."Neighborhood", $h."Custom_field_1", $h."Custom_field_2", 
     $h."Custom_field_3", $h."Custom_field_4", $h."Custom_field_5" -join "," | 
         Out-File $filename -Append -encoding utf8
    
     # $item is an array ordered by the "Order_Reference" property
     For ($i = $RememberLastOrderPosition; $i -lt $item.count; $i++){
         if ($h.OrderNumber -eq $item[$i].Order_Reference){
             # emit order item
             $_."Record_Type", $_."SKU_Code", $_."SKU_Description", $_."Quantity", $_."Item_Price", $_."Unit_Weight", $_."Weight", $_."length", $_."width", $_."height", $_."Dimensions_UoM", 
             $_."HS_Code", $_."Item_Origin", $_."ImageURL", $_."Non_Returnable", $_."Dangerous_Goods", $_."Export_Date", $_."Export_AWB", $_."Export_Carrier_Name", $_."SKU_URL", 
             $_."Tracking", $_."Custom_field_1", $_."Custom_field_2", $_."Custom_field_3", $_."Custom_field_4", $_."Custom_field_5", $_."Days for return", $_."CostPrice" -join "," | 
                 Out-File $filename -Append -encoding utf8
         }
         elseif ($h.OrderNumber -lt $item.Order_Reference) {
             # either no order line item was found,
             # or all line items for order have been processed
             $RememberLastOrderPosition = $i # pick up search for next order where it was left off
             break   # get the next order header
         }
     }
 }
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.