5 Replies Latest reply on Dec 11, 2015 10:51 AM by Marcos Vainer Loeff

    How do you search by digitalset status with GetPointsSQL?

    Jeevan

      Hi,

       

      Apologies if this is a noob question.

       

      I'm trying to use the GetPointsSQL method to search by pointsource and the current value of the tags. All the tags belonging to the pointsource have get values in a digitalset. I've tried the following but they all result in an 'Invalid where clause' error.

       

      GetPointsSQL("pointsource='myps' AND Status='valueOfDigitalSet' ")

      GetPointsSQL("pointsource='myps' AND status='valueOfDigitalSet' ")

      GetPointsSQL("pointsource='myps' AND Tag.Status='valueOfDigitalSet' ")

      GetPointsSQL("pointsource='myps' AND PIpoint.Status='valueOfDigitalSet' ")

      GetPointsSQL("pointsource='myps' AND PIcomp.Status='valueOfDigitalSet' ")

       

       

      As an aside, when you use the 'Advanced Search' tab (in SMT Tag Search), you can see the SQL where clause which you can copy into your code. Is there a way to do the same with the 'Basic Search' tab?

        • Re: How do you search by digitalset status with GetPointsSQL?
          Marcos Vainer Loeff

          Hello Jeevan,

           

          If you take a look at this thread, you will see an example of using GetPointsSQL method not only with the PIPoint table but also with the PIComp which will let you filter data according to the values.

           

          I have done some tests and it seems that the performance is low.

           

          That is why I think the solution below is better, especially if performance is a concern. I use GetPointsSQL() to filter the PI Points with an specific digital set and then I add more PI SDK code to filter the PI Points according to their values.

           

           

           PISDK.PISDK sdkroot = new PISDK.PISDK();
                      Servers piServers = sdkroot.Servers;
                      Server piServer = piServers["MARC-PI2014"];
          
          
                      PointList filteredPIPointList = new PointList();
                      PointList finalPIPointList = new PointList();
          
          
                      filteredPIPointList = piServer.GetPointsSQL("PIpoint.DigitalSet = 'OnOff'");
                      NamedValues nvErrors = null;
                      PointValues pointValues = filteredPIPointList.Data.get_Snapshot(out nvErrors);
                      foreach(PointValue ptValue in pointValues)
                      {
                          if (ptValue.PIValue.Value is DigitalState)
                          {
                              string digitalValue = ((DigitalState)ptValue.PIValue.Value).Name;
                              if (digitalValue == "Off")
                              {
                                  finalPIPointList.Add(ptValue.PIPoint);
                                  Console.WriteLine(ptValue.PIPoint.Name);
                              }
                          }
                  
                      }
                      Console.ReadKey();
          

           

          Hope it helps!

            • Re: How do you search by digitalset status with GetPointsSQL?
              Jeevan

              I'm currently doing something like what you've suggested:

               

              PISDK.PointList points = server.GetPointsSQL("pointsource='my-source'");

              for(int i = 1; i <= points.count; i++)

                          {

                              var point = points[i];

                              if (point.Data.Snapshot.Value.Code != 2)

                              {

                                  MyList.Add(new KeyValuePair<int, string>(point.Data.Snapshot.Value.Code, point.Data.Snapshot.Value.Name));

                              }

                          }

               

              As you can make out, this checks for the points whose current value is not 2 and adds them to the list. This is iterating over roughly 50, 000 tags and is currently taking about 7 minutes to run. This is way too much. I thought if I could add the value filter to the GetPointsSQL method, it will return fewer points (about 500).

               

              Which method would you suggest in this scenario? Performance is the bottleneck for me right now.

                • Re: How do you search by digitalset status with GetPointsSQL?
                  Marcos Vainer Loeff

                  Hello Jeevan,

                   

                  Here are my recommendations for you:

                   

                  • You have only filtered the PI Points through the pointsource. Are all PI Points from this pointsource digital tags? Are those PI Points using the same digital set? You should filter as much as possible on the GetPointsSQL method to reduce the number of filtered PI Points.

                   

                  • My approach calls PointList.Data.get_snapshot(). With a unique call, I am getting the snapshot values from all the PI Point list. Your approach calls the Snapshot() method 50.000 times. As a result, my approach should have a better performance. Nevertheless, if the number of PI points on the list is too high, you can also have performance issues. All in all, you need to test and compare.
                  • PI AF SDK should have an even better performance. Migrating your code from PI SDK to PI AF SDK should not a difficult task to do.
                    • Re: How do you search by digitalset status with GetPointsSQL?
                      Jeevan

                      Hi Marcos,

                       

                      • Yes, all points from that point source are digital tags and use the same digital set.
                      • This is great. I didn't know it does that. Although I'm still not sure I get it. I used your method and it is without doubt, much faster. However, on inspection of the properties, it looks like it still lazy loads everything but in the foreach loop, it seems very quick.
                      • AF is not an option unfortunately at the moment.

                       

                      Thank you so much for taking the time to answer.

                        • Re: How do you search by digitalset status with GetPointsSQL?
                          Marcos Vainer Loeff

                          Hello Jeevan,

                           

                          I am glad to hear that with the provided code snippet, your application is running much faster!

                           

                                  PointValues pointValues = filteredPIPointList.Data.get_Snapshot(out nvErrors); 
                          

                           

                          When you run the method above, the client will make a unique RPC call and get all snapshots from all PI Points on the list. On the foreach loop, you are just accessing data stored on the local memory that it was already retrieved from the PI System.

                           

                          On the foreach loop from your code snippet, each PI Point will make an RPC request when you access the property PIPoint.Data.Snapshot. As this data is not on memory as on the previous scenario, it needs to be retrieved from the PI System. Therefore, if you have 50.000 PI Points, it will make 50.000 RPC calls. That is the reason that explains the observed slowness.

                           

                          Let me know if it is clear now!