20 Replies Latest reply on Aug 7, 2017 9:25 AM by gregor

    Proper way to delete large ammount of values from Pi Archive


      Hi,  following this discussion


      The PIServerFeature 'Delete Range Feature' is not supported on PIServer 'PI Data Archive '


      I concluded that for deleting range of values in Pi archive it is better to be used PI Sdk method instead of Af Sdk, because of better performance and no need to retrieve the values first (in case of using archive 2015 or older, not that we need to support it):


                  point.Data.RemoveValues(piTimeStart, piTimeEnd, DataRemovalConstants.drRemoveAll)

      However, a bit of surprise: the method above whan called for a large number of values (cannot tell how large exactly, but genrally if I try to delete the values of 10 hz time series and could even call it for a months of 10 Hz data) the method passes succesfully and throws no errors but the values are still there! In another situation - if delete this 10 hz timeseries using smaller period - it is slow and sometimes timeouts even with 20 min period (12 000 values in Pi).


      As discussed in the topic above, if AF variation option is chosen (for archive older than 2016) - it is no suitable for deleting large portions of data, because crashes when retrieving the data (and also the performance seems worse that the Pi Sdk version).


      So, what is the proper way to delete large ammounts of archive values?


      Thank you in advance for your help!

        • Re: Proper way to delete large ammount of values from Pi Archive

          Hi Varban,


          I would recommend taking a look at this KB: https://techsupport.osisoft.com/Troubleshooting/KB/3065OSI8/


          May I ask why you want to delete archive data?  Are you going to replace the data with something else, or are you trying to save archive space, or is there another reason?

          • Re: Proper way to delete large ammount of values from Pi Archive

            Hello Varbin,


            From the SDK help examples, I see this used for error logging:

            Set ptval = srv_write.PIPoints.Item(Text2.Text)
              Set piErr = ptval.Data.UpdateValues(vals, GetdmMode(Combo1))
              If Not piErr Is Nothing Then
                   If piErr.Count > 0 Then
                        Err.Description = "Received " & piErr.Count & " error: " & _
                        Err.Raise -1, "UpdateValues", Err.Description
                   End If
              End If


            The above was used in conjunction with UpdateValues. I would expect this methodology would also be possible with RemoveValues, as well. Please forgive if you have already tried this or similar.




            • Re: Proper way to delete large ammount of values from Pi Archive
              Dan Fishman

              Interesting and it sounds like a timeout... I've been using the AF SDK quite a lot to delete values using both delete methods the past few days, but only hundreds of values at a time.  My main reason for this is due to collectives and buffered deletes not are supported using the PI SDK.


              My understanding is that the PI SDK is reading the values under the hood for you and is more efficient than AF SDK updatevalues methods (with a recorded call first) since there is less overhead.  I always thought the PI SDK was reading the value internally and sending it back with an archive mode of remove.  Also, I would think the new delete range feature is the most efficient.  It sounds like you might have to batch up your delete calls.


              Also, you might want to reprocess the archives after massive archive manipulations!  I'm curious what else we find out.




              • Re: Proper way to delete large ammount of values from Pi Archive

                Hi all, thank you for your suggestions!


                @Keith, it seems that the known issues are not related to my case. The reason I want to delete values is that i write values in pi depending on some configuration and application logic making some calculations. When this configuration is changed, different values should be written in pi and the existing until now should be deleted (i.e values need to be replaced).


                @Richard, unfortunatelly the RemoveValues method has only one overload which is void, it throws an exception if there is an error (e.g the time out I'm mentioning - in case of time out there is an exception, but in the cases i do not have a solution for, it just passes and the values are not deleted)


                @Dan, you are probably right in your observations, but note that the range delete with af sdk cannot be used in my case, because it is not supported by archive, older than 2016 (I haven't tested it).

                  • Re: Proper way to delete large ammount of values from Pi Archive
                    Rick Davin

                    Since the version of AF SDK that you are using eliminates using PIPoint.ReplaceValues, you will need to:


                    1. Pull the AFValues you wish to delete from the PI Data Archive using PIPoint.RecordedValues or a related method.
                    2. Push those AFValues back to the Data Archive using PIPoint.UpdateValues and pass in AFUpdateOption.Remove.


                    The big thing to be aware of is that the ArcMaxCollect tuning parameter is set to 1.5 million, so you may pull and push - at most - 1.5 million values.  Since you indicate you have more data than that,  you will need to incorporate some mechanism to reduce the number of values in a given pull+push instance.  In some previous applications, my preferred way of doing this was by chopping up the time range.  And I tried to aim for around half-a-million values at a time.  Sometimes it was a bit more than that.  Sometimes it was less than that.  But the key thing was to stay well below the 1.5 million threshold.


                    In case you have the knee-jerk reaction to increase ArcMaxCollect to a ridiculously high value, DON'T.  Not only does this require a PI Server restart, it also stresses the Data Archive.  And that setting isn't just for you.  Every user of that Data Archive is then free to ask for ridiculously large value counts.  A smart developer knows not to "give away the candy store" in attempts to making things easier on himself.  Instead learn to chop up huge monolithic calls into more manageable sizes.


                    That, or upgrade your PI Data Archive so that you can use PIPoint.ReplaceValues.

                    1 of 1 people found this helpful
                  • Re: Proper way to delete large ammount of values from Pi Archive

                    Hi Rick, thank you for your suggestion.
                    I've used it on several occasions myself too for retrieving values which could be more than 1.5 mln (making multiple calls).
                    I tried this too in this case (but using Pi sdk, not AF Sdk) without success.
                    However as was pointed out in the pervious topic, deleting with pi sdk is not much different than deleting with af sdk, and since Pi Sdk is timing out with the range of 12 000 I would be surprised if AF Sdk manages to delete 1.5 mln.
                    Nevertheless, I'll try and let you know.

                    • Re: Proper way to delete large ammount of values from Pi Archive

                      Hi Varban, What I have done previously is batched the data into n Days at a time for deletes. If you try to delete too much data at a time it most likely times out. It takes a while batching the data up but ensures that the deletes happen as expected. This is the best I have managed across the years.

                      • Re: Proper way to delete large ammount of values from Pi Archive

                        Hi, Neil, Janos, thanks a lot for sharing your experience!
                        I really hope someone who already know the answer to share it.


                        First, let me hereby confirm that i do not see a difference between af sdk approach Rick proposed and Pi sdk approach I am using - I managed to get timeouts as successfully with AF sdk as with Pi sdk


                        The problem is that I already use this technique which Rick, Neil and Janos mention since the beginning.  As I mention in the first post - trying to delete large periods results in nothing happening. Trying to delete small consecutive periods - often slow, sometimes timeouts (even with relatively insignificant amount of values - 12000).


                        Since many people seems to have experience in which they succeeded to delete, there is something specific in my case which is causing the problem, therefore I'll explain a bit the application logic:

                        - I retrieve many values from PI, use them for some calculations and after that i must write in pi the results of this calculation (something like custom ACE module )

                        - If this is happening for the past, I need to delete large portion of old values and insert the new ones in their place.
                        - I try to delete first the whole period - this in some cases can be successful, if it is not - nothing happens, just the values stayed.
                        - after that two threads begin to do delete values if any and insert new values in several time series, by periods of 20 minutes. First the values in each time series are deleted and after that new values are being inserted for this period of 20 minutes. Most of the time series for which this is done has small amount of values (1 per 20 minutes), the 10 Hz time series are only two. The whole logic applied to them  (delete first and insert after) is done by two threads again (i.e it is possible that 4 threads are being deleting at the same time).


                        The unanswered or new questions that came to mi mind are:


                        1. Still - it is not normal that

                           point.Data.RemoveValues(piTimeStart, piTimeEnd, DataRemovalConstants.drRemoveAll)

                        to not throw an exception and to not delete anything - why is this happening, is there something wrong?


                        2. Is it possible that the behavior is caused by the archive load? Of course archive is under stress when this deleting/inserting many values is happening, so it is certainly contributing. But! As I have measured the different operations, the only thing which is slow is the deletes. The inserts and retrievals are ok.


                        3. Is it possible that multithreading is a problem? In my experience it is not something that is a problem for the PI server usually?


                        4. Is it possible that not multithreading, but the fact, that I try to delete from same time series two ranges at the same time?


                        5. Could the behavior be because of some setting of a Pi Point itself (e.g. currently -Archiving - on, Compressing - off,  Exception Deviation - 0)


                        6. Is there a reasonable expectation that using archive 2016 (or 2017) would help, with or without using the new PIPoint.ReplaceValues


                        7. In regards with above - I would like to try that but cannot download the archive version 2016. In tech support i do not have a Download link, but instead only a "Renew". I've sent some request there yesterday but there is no answer yet.


                        Thank you very much for your replies and for the patience to read all this!