AnsweredAssumed Answered

OSIsoft.PowerShell: Data deletion hangs

Question asked by André Åsheim Champion on Nov 12, 2015
Latest reply on Nov 13, 2015 by pthivierge

Hi,

We have a script that deletes data every night, but after upgrading the PI Data Archive to 2015 it hangs almost every time.

Any suggestion on how to optimize this process?

 

 [DateTime]$CurrentProcessingTime = $DateProcessingStartTime
        $TimeNowQ = Get-Date
        Write-Host $TimeNowQ "Starting step: Data deletion"
        Write-Output " "
            $connectionforDel = New-Object System.Data.OleDb.OleDbConnection
            $connectionforDel.ConnectionString = "Provider=PIOLEDB; Data Source="+$DataSource+";"
            $connectionforDel.Open()        
            while ($CurrentProcessingTime -lt $DateProcessingEndTime)
            {
                $PISnapShotStatistics = Get-PISnapshotStatistics -Connection $PIDataArchive
                $PIServerEventQueue = $PISnapShotStatistics.GetValue(7).Value
                $QueryIntervall = 10

                if($PIServerEventQueue -gt 100){
                $TimeNowQ = Get-Date
                Write-Host $TimeNowQ "Waiting will not continue until events in the queue is less than 100"
                Write-Output " "
                $TimeNowQ = Get-Date
                Write-Host $TimeNowQ "Events in queue:" $PIServerEventQueue "waiting 5 seconds"
                Write-Output " "
                Start-Sleep -Seconds 5
                }
                elseif($PIServerEventQueue -lt 100){
                    [DateTime]$QueryStartTime = $CurrentProcessingTime
                    [DateTime]$QueryEndTime = $CurrentProcessingTime.AddMinutes($QueryIntervall)
                    $QueryStartCulture = [DateTime]::Parse($QueryStartTime,([Globalization.CultureInfo]::CreateSpecificCulture('en-US')))
                    $QueryEndCulture = [DateTime]::Parse($QueryEndTime,([Globalization.CultureInfo]::CreateSpecificCulture('en-US')))
                    $QueryStart = '{0:dd-MMM-yy HH:mm:ss}' -f $QueryStartCulture 
                    $QueryEnd = '{0:dd-MMM-yy HH:mm:ss}' -f $QueryEndCulture
                    
                    $TimeNowQ = Get-Date
                    Write-Host $TimeNowQ "Deleting data for start time:" $QueryStart "end time" $QueryEnd
                    Write-Output " "
                    [string] $delquery = "Select tag From pipoint..classic sub where sub.userint1=1 and sub.userint2="+$ProcessingStartTime+" and sub.pointsource='"+$PointSource+"'"
                    $Delcommand = New-Object System.Data.OleDb.OleDbCommand
                    $Delcommand.Connection = $connectionforDel
                    $Delcommand.CommandText = $delquery
                    $delreader = $Delcommand.ExecuteReader()
                    while ($delreader.read()) {
                        $CurrentPITag = Get-PIPoint -Connection $PIDataArchive -Name $delreader.GetValue(0)
                       # $TimeNowQ = Get-Date
                        #Write-Host $TimeNowQ "Deleting data on tag:"  $CurrentPITag "start time:" $QueryStart "end time:" $QueryEnd
                       # Write-Output " "
                        Remove-PIValue -Connection $PIDataArchive -Event (Get-PIValue -PointName $delreader.GetValue(0) -StartTime $QueryStartTime -EndTime $QueryEndTime -Connection $PIDataArchive) 
                    }

                    Write-Output " "
                    [DateTime]$CurrentProcessingTime = $CurrentProcessingTime.AddMinutes($QueryIntervall)
                }
                else{
                $TimeNowQ = Get-Date
                Write-Host $TimeNowQ "Unable to get PI server event queue."
                Write-Output " "
                $TimeNowQ = Get-Date
                Write-Host $TimeNowQ "Exiting..."
                Write-Output " "
                $ProsessTimeFinished = Get-Date
                Add-PIValue -Value $ProcessFinishedValue -PIPoint $ProcessTag -Timestamp $ProsessTimeFinished 
                $Connectionfordel.Close()
                $connection.Close()
                exit
            }

            }
        $TimeNowQ = Get-Date
        Write-Host $TimeNowQ "Completed: Data deletion"

Outcomes