Rick DavinIt does use the PI SQL Subsystem.
It makes me wonder if GetPointsSQL or the SQL Subsystem uses PIAPI under the hood.
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.
Ouch, I know we all know PISDK has an overhead compared to API but why is it such a difference?
(Great post Rick!)
Re-read documentation on GetPointsSQL. I am using PISDK 22.214.171.1247. 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.
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.
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!
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"?
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.
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.
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.
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.
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.