[Powershell]Performance van CSV file verwerking verbeteren

[The-Source]

Ik heb een csv files welke afhankelijk van eerdere data zo'n 35 a 40MB groot is (niet heel bijzonder groot lijkt mij). Dit bestand bestaat uit 4 kolommen en z'n 400k regels.
Ik heb nu 2 varianten van verwerking gemaakt maar de performance vind ik gewoon zeer matig.
Code volgt onder deze tekst..
Variant 1 maakt gebruik van import-csv functie wat al voor de nodige vertraging zorgt voordat het echt begint te verwerken. En uiteindelijk duurt de verwerking iets meer dan een uur
Variant 2 gebruikt ik streamreader, de start is dan al sneller maar ook al schrijf ik in die variant per 800 regels de data weg naar de SQL database duurt het geheel nog steeds 50 minuten. (Op AMD Ryzen 5 Pro 2500U met 8GB)
Dus ook niet echt de performance boost waar ik op zat te hopen.

variant 1 (import-csv)

Visual Basic .NET:

$TotalRows = 0
$reader = New-Object IO.StreamReader "$global:Logdir\Newfile.csv"
 while($reader.ReadLine() -ne $null){ $TotalRows++ }
 $reader.Dispose()

$actlog = Import-CSV "$global:Logdir\Newfile.csv"
ForEach ($row in $actlog) {
  $Rowcount=++
  $locatie = $row.('ai').Split(':')[1] #headers are created by previous export
  $oproeppunt = $row.('aj').Split(':')[1]
  $tijdstip = $row.('j').Split(':')[1] #2019-06-25 09.30.38 must be change to: 2019-06-25 09:30:38
  $tijdstip = $tijdstip.replace('.',':')
  $Status = $row.('ab').Split(':')[1]
  LogToevoegen $global:pnummer $locatie $oproeppunt $tijdstip $Status
  Write-Progress -Activity "Processing lines from CSV.." -PercentComplete (($Rowcount*100)/$TotalRows) -Status "$(([math]::Round((($rowcount)/$TotalRows * 100),0))) %"
} #end foreach filling database log

"All done!"

Variant 2: (streamreader)

Visual Basic .NET:

$TotalRows = 0
$rowcount = 0
$sqlrowcounter = 0
$reader = New-Object IO.StreamReader "$global:Logdir\Newfile.csv"
 while($reader.ReadLine() -ne $null){ $TotalRows++ }
 $reader.BaseStream.Position = 1
 while(($Tempdata = $reader.ReadLine()) -ne $null){
    if ($rowcount -eq 0) {$rowcount = $rowcount + 1} else {
    $splitvar = '","'
    $row = $Tempdata -Split($splitvar)
    
    if ($sqlrowcounter -eq 0) { $SQLstring=''} #end if
    if ($sqlrowcounter -ne 0)  { $SQLstring = $SQLstring + ','}

  $sqlrowcounter= $sqlrowcounter + 1
  $Rowcount= $Rowcount + 1
  $locatie = $row[0].Split(':')[1] 
  $oproeppunt = $row[1].Split(':')[1]
  $tijdstip = $row[2].Split(':')[1] #2019-06-25 09.30.38 must be change to: 2019-06-25 09:30:38
  $tijdstip = $tijdstip.replace('.',':')
  $Status = $row[3].Split(':')[1]
  $Status = $Status.Substring(0, $Status.Length -1) 
  if (($null -ne $locatie) -and ($null -ne $oproeppunt) -and ($null -ne $tijdstip) -and ($null -ne $Status)){
       $SQLstring = $SQLstring + "($global:pnummer, '$locatie', '$oproeppunt', '$tijdstip', '$Status')"
      }
  if ($sqlrowcounter -eq 800){
      LogToevoegenBatch $SQLstring
      $sqlrowcounter = 0
      }

Write-Progress -Activity "Processing lines from CSV.." -PercentComplete (($Rowcount*100)/$TotalRows) -Status "$(([math]::Round((($rowcount)/$TotalRows * 100),0))) %"
    } #end if rowcount = 0
} #end while
$reader.Dispose()
$reader.Close()
If ($sqlrowcounter -gt 0){
     LogToevoegenBatch $SQLstring
  } #needed write the last part to the database
"All done!"

Het CSV bestand waar de data uitkomt ziet er als volgt uit:

code:

"ai","aj","j","ab"
"Text:C477P.","Name:C477P","Time:2020-01-31 23.10.36","NI_State:Set"
"Text:B343P","Name:B343P.","Time:2020-01-31 23.10.52","NI_State:Set"
"Text:B343P.","Name:B343P","Time:2020-01-31 23.10.52","NI_State:Clear"
"Text:B343P","Name:B343P.","Time:2020-01-31 23.10.53","NI_State:Clear"
"Text:C477P","Name:C477P.","Time:2020-01-31 23.11.50","NI_State:Set"
"Text:C477P.","Name:C477P","Time:2020-01-31 23.11.51","NI_State:Clear"
"Text:C477P","Name:C477P.","Time:2020-01-31 23.11.51","NI_State:Clear"

De reden dat ik de if ($Rowcount -eq 0) erin heb staan is dat de streamreader niet de eerste regel wil overslaan en bij import-csv wordt deze gelijk als kolom naam gebruikt. Deze data naar de SQL database sturen gaat niet goed als er bijvoorbeeld een datum wordt verwacht.

Nu heb ik ook gelezen dat je een CSV in een dataset kan laden maar zal dat echt de nodige performance boost geven voordat ik daar nog een andere test variant van ga maken?

[superduper]

Is python/Perl geen optie?

[itons]

En zonder de write-progress?

[Vloris]

Het klinkt een beetje alsof je een vaag vermoeden hebt dat je performance-probleem ligt in het lezen van de CSV.

Je zult toch eerst erachter moeten komen wat er traag is: is dat het lezen van de CSV of het schrijven van de resultaten?
Je hebt nu twee varianten van het lezen geprobeerd en dat lijkt weinig effect te hebben, dan zou ik zeggen: vergelijk deze twee eens met elkaar als je het 'LogToevoegen' deel helemaal weg laat.

[The-Source]

itons schreef op dinsdag 21 april 2020 @ 12:31:
En zonder de write-progress?

Deze is nu net gestart en alle $rowcount verwijzingen er ook gelijk uit

[Rolfie]

Zit de vertraging echt op het inlezen, de row/string aanpassingen of in het SQL statement?

Ik zou daar eerst naar kijken, waar exact de vertraging in zit. Daarna kun je kijken of je dit kunt optimaliseren.

SQL kan ook een behoorlijke vertraging opleveren.

[ Voor 4% gewijzigd door Rolfie op 21-04-2020 12:39 ]

[The-Source]

Ik heb zojuist de suggestie van itons toegepast en net voor het posten van dat die gestart was aangezet. En hij is al weer voordat ik dit begon te typen klaar.
Dat is een aanzienlijke verbetering
Ik wist niet dat een leuk status overzichtje z'n vertraging kon geven

[ Voor 16% gewijzigd door The-Source op 21-04-2020 12:39 ]

[itons]

The-Source schreef op dinsdag 21 april 2020 @ 12:39:
Ik heb zojuist de suggestie van itons toegepast en net voor het posten van dat die gestart was aangezet. En hij is al weer voordat ik dit begon te typen klaar.
Dat is een aanzienlijke verbetering
Ik wist niet dat een leuk status overzichtje z'n vertraging kon geven

Je moet er een keer tegen aanlopen, maar Write-Progress is echt tergend sloom. http://linkredglue.com/po...-scripts-performance-tip/

[The-Source]

itons schreef op dinsdag 21 april 2020 @ 12:47:
[...]


Je moet er een keer tegen aanlopen, maar Write-Progress is echt tergend sloom. http://linkredglue.com/po...-scripts-performance-tip/

Dat gelezen te hebben is 400K updates sturen wat overdreven
Ik doe het nu gewoon via "Write 1000 rows to SQLdatabase. Total of $Verwerkt/$TotalRows rows are processed "
Geeft mij voldoende indruk dat het script loopt en van 50minuten duurt het nu 1m51s
SQL heeft ook een limiet van 1000 rows per keer maar daar kan ik prima mee leven.

[FredPlacemet]

Als je echt snel bestanden wilt uitlezen moet je een keer naar logstash kijken