11 Replies Latest reply on Oct 31, 2011 10:41 PM by jlakumb

    GetPointsSQL versus GetPoints

    Rick Davin

      In a recent post, http://vcampus.osisoft.com/forums/t/819.aspx?PageIndex=2, I commented about that PI OleDb was much faster than PISDK GetPoints when hitting a PI server with lots of tags.  I have over 1.7 million tags.  Using GetPoints to return 9000 PI Points takes over 3 minutes whereas PI OleDb can return the 9000 tag names in 15 seconds.  Just stating a fact. Basically I am doing a simple search such as:


           _piserver.GetPoints("Tag = '*pH'")


      Rhys, caching or the order in which I make the request has no bearing.


      Steve, yes I tried using the GetPoints2 method and it to takes over 3 minutes.  I too was curious as to why PI OleDb was that much faster when it too uses the PISDK.  Here's some more info:


      Using PIDlg.TagSearch takes 15 seconds or so.  However, it is unacceptable for my application because I do not want to prompt the user to select tags.  The user need only select the PI server and the app decides on the tags.


      What I did discover is that by using GetPointsSQL method takes 10 seconds!  The filter can be the same or it can be "Tag like '*pH'" for the same results.  I avoided GetPointsSQL for one reason because the documentation says it can perform complex queries.  My query is simple and I am simple enought to think Complex=Slower.  I avoided GetPointsSQL for another reason because the documentation says that the SQL Subsystem limits tag names to 80 characters.  Yes, we do have tags > 80 characters although there are select applications I am writing where I know the tags are < 80 chars.


      Now I am curious.  When I see a limit of 80 char tag names, I tend to think of PIAPI.  It makes me wonder if GetPointsSQL or the SQL Subsystem uses PIAPI under the hood.

        • Re: GetPointsSQL versus GetPoints

          Rick Davin

          It makes me wonder if GetPointsSQL or the SQL Subsystem uses PIAPI under the hood.
          It does use the PI SQL Subsystem.




          Per the PI SDK Programming Reference:

          The call makes use of the underlying Server's SQL subsystem and follows the syntax described in the OSI PI-ODBC manual. There are cases where this syntax does not match the attribute names available from the PointAttributes collection in the PI-SDK though it is still valid for this call. In addition, there are attributes available through the PI-SDK (including those from user defined Point Classes) which are not recognized by this routine as it is based on the SQL implementation. To perform a query against these attributes, use the Server.GetPoints method.

          • Re: GetPointsSQL versus GetPoints
            Rick Davin

            Re-read documentation on GetPointsSQL.  I am using PISDK  And my PI servers are 3.4.380.36.


            GetPointsSQL can implement complex queries, including joins with other PI tables. GetPoints cannot do such complex queries, but it is faster, and can use custom point attributes, which GetPointsSQL cannot.


            OK, so the documentation is telling me GetPoints is faster than GetPointsSQL but my evidence shows that's wrong.


            The SQL subsystem limits the size of strings returned.  For this reason, tag names located by the SQL subsystem will be no more than 80 characters in length.  If you have tags with longer names use the GetPoints call. 


            Again, I have evidence to suggest otherwise.  I queried all tags on a PI server with 13,600 points using GetPointsSQL.  Among those points were 125 tags where the tag name was longer than 80 chars.  The longest tag name was 91 chars.


            So looks like the documentation is wrong (at least for the versions I am working with).  Not that I'm complaining.  If GetPointsSQL returns names > 80 chars and is much faster than GetPoints, then I'll gladly use it despite what the documentation says.









              • Re: GetPointsSQL versus GetPoints

                Hi, good discussion, and not at all clear-cut.


                A few descriptions that may help define your tests further:

                • GetPoints (or it's various enhanced interfaces) uses the pibasess subsystem directly to receive it's answer.  It only handles it's own PointDb, not archive or snapshot, so it can't do joins.
                • GetPointsSQL uses pisqlss which is a separate subsystem that fetches from whatever database tables are needed.
                • PIODBC uses the pisqlss through the PIAPI.
                • PIAPI pipt_* functions call the translator (pinetmgr) which talks to pibasess.
                • The PISDK keeps a local cache of PIPoint objects.  Any call after the first will be much faster when returning the same PIPoint objects.
                • Tag search dialog has two modes, Basic search which uses GetPoints, and Advanced search which uses GetPointsSQL.  This suggests a test by launching TagSearch, connecting, then timing each of the two searches with a restart in between tests.
                • I believe PIOLEDB uses GetPoints to retrieve PIPoints.


              • Re: GetPointsSQL versus GetPoints
                Rick Davin

                OK, I've done some digging and looks like the mystery is solved.  GetPoints, GetPointsSQL, and PI OleDb all execute in about the same time.


                The problem was that in my original app using GetPoints, I also passed a PIAsynchStatus object.  I then had a loop going on until the status completed so that I could offer the end-user an Abort button and also echo back the ProgressPercent (not unreasonable features in an app).


                PI OleDb does not issue a call with PIAsynchStatus, so it executed in the 15 seconds instead of 3+ minutes.  When I first tested GetPointsSQL, I did not use PIAsynchStatus, so it too executed in 15 seconds.  But if I use PIAsynchStatus with GetPointsSQL, it's back to 3+ minutes.  And the good news is that when I use a plain GetPoints without PIAsynchStatus, it runs in 15 seconds as well.


                I do apologise for presenting wrong results.  Well, they weren't exactly wrong but needed better context.  On the other hand, it's been an informative thread!



                  • Re: GetPointsSQL versus GetPoints

                    Glad you were able to sort this out!


                    However there's a little something that bugs me: executing something asynchronously (i.e. passing a PIAsynchStatus) is usually more efficient than executing the same method in a synchonous manner (passing null for the AsynchStatus argument). Maybe it has to do with what you do "around" the tag search (e.g. checking the status, the progress bar)? Or simply how you handle the call back of the method?


                    If you want to dig a little deeper on the asynchronous execution, maybe share some code, etc., I suggest we do that in a separate discussion thread since this is a relatively different topic than your original GetPoints vs. GetPointsSQL. You can also take a look at this other discussion thread, where Andreas showed an example of executing something asynchronously, but in a single-threaded application. Maybe start from there to steer the discussion towards "real multi-thread, asynchronous execution"?

                    • Re: GetPointsSQL versus GetPoints

                      Rick Davin

                      PI OleDb does not issue a call with PIAsynchStatus, so it executed in the 15 seconds instead of 3+ minutes.

                      Well, sorry to be contrary, but PIOLEDB does indeed make asynchronous calls for GetPoints.  There may be something else going on in this case that we have not yet identified.


                      Asynchronous calls do have more overhead than synchronous calls.  However, on the scale of 15 seconds, the overhead should not be noticable.  The advantage of asynchronous calls (in addition to those you already mentioned - cancel and status updates), is to allow concurrency and to ensure the application is not left in a blocking state.  Allowing multple threads of execution for operations will scale better for single threaded apps, but on multiple cores, we may begin seeing much better processing capacity.


                      I was at the MS PDC last fall, and concurrency is the new wave of optimization that everyone expects to keep the software industry capable of handling the massive increases in data quantity we are seeing.

                        • Re: GetPointsSQL versus GetPoints
                          Rick Davin

                          Thanks for you reply, Charles.  I'm quite sure that under the hood, PIOLEDB makes asynchronous calls and that it uses GetPoints.  But again that's under the hood and not exposed to me when I am working with ADO.NET objects.  When I make an OLEDB call, ADO is oblivious that the PIOLEDB provider makes asynchronous calls with PIAsynchStatus.  Unlike when I call GetPoints and I can pass a PIAsynchStatus object, I can't do the same when I am working with ADO objects.  That's what I meant by my previous statement.

                            • Re: GetPointsSQL versus GetPoints

                              I don't know if this is common knowledge, but one thing occured to me while using these functions.


                              I was (re)creating a tagsearch in Silverlight, and I just created a 'where query' from the textboxes on my page. So, every query was 'Where Tag = '*sin*' and pointtype = '*' and pointsource = '*' and descriptor = '*' and engunits = '*''. This made the query encredibly slow, if I just ommited the '*' values, the query was much much faster.


                              This is also described in the PISDK documentation, but I didn't know it at that time.

                                • Re: GetPointsSQL versus GetPoints

                                  Albeit late, you may be happy to hear that we made significant performance improvements in tag searching in PI Server 2012. Since this is an old thread I imagine you found a solution to your issue, but I just wanted to share with all.

                                    • Re: GetPointsSQL versus GetPoints

                                      Here is a recent internal discussion regarding GetPointsSQL:


                                      We noticed the following when playing around with the Advanced Tag Search dialog in PI SDK:


                                      The PI SDK “Advanced Tag Search” does not use the DISTINCT keyword.  This means a lot of unnecessary data is sent across the wire.  To work around this, users can actually specify the DISTINCT keyword in the WHERE clause because the following two syntaxes are supported by the PI SQL Subsystem in PI Server.


                                      SELECT DISTINCT PIPoint.pointnumber FROM PIcomp,PIpoint  WHERE PIpoint.Tag = PIcomp.Tag AND PIcomp.Time > "21-oct-11 00:00" AND PIcomp.Time < "21-oct-11 00:01";


                                      SELECT PIPoint.pointnumber FROM PIcomp,PIpoint  WHERE DISTINCT PIpoint.Tag = PIcomp.Tag AND PIcomp.Time > "21-oct-11 00:00" AND PIcomp.Time < "21-oct-11 00:01";


                                      Note that we added PLI #25101OSI8 (Advanced tag search should use DISTINCT keyword for pisqlss in GetPointsSQL queries) for this enhancement request.