In this video we're going to explore the performance improvement and schema
simplicity of RTQP or real-time query processing
versus PI OLEDB enterprise. Let's begin by opening PI SQL commander.
In this video we're gonna use the wind farm database as it is a sufficiently large
enough AF testing database to show performance differences between RTQP and PI OLEDB enterprise.
I've prepared two queries one for PI OLEDB enterprise and one for RTQP. So let's begin
by opening that file now. Each of these queries return the same output which is the element name,
the path and name of the attribute, the value type for the attribute and the unit of measure. Right
away we can see the query simplicity of PI SQL client versus that of PI OLEDB enterprise. For
PI SQL client everything we need is contained within the attribute table whereas for PI OLEDB
enterprise we need to join two different tables to the element table to get what we're looking
for. This is an artifact of the denormalized nature of the RTQP schema versus that of
the highly normalized nature of the PI OLEDB enterprise schema. I would like to run these
queries side-by-side so I'm going to copy the PI SQL client to a new window. Now let's connect to
both of our data sources so we can run the queries. Here we see the wind farm database,
for PI SQL client let's create a new connection specifically for this database. Great now let's
assign the connections to each of the query windows. We'll right-click on the
RTQP one and select connection and then the RTQP connection. For the OLEDB enterprise we'll do
the same thing. Let's select the AF server. Now let's run the OLEDB enterprise query.
Now let's look at the execution time. This query took almost twenty one and a half seconds to
return the first row and an additional few seconds to return all the rows totaling up
to 377,000. Now let's repeat this process for the RTQP version. Notice that we started seeing
results almost immediately and it only took less than five seconds to return the whole
thing. The execution time of the query was 0.4 eight seconds and it took almost five seconds to
turn all 377 thousand rows. There are two different concepts that are combining to
produce this result. The first is that the query execution is done server-side for the RTQP query
however the execution is done client-side for the PI OLEDB enterprise query. Because of this
the execution is substantially faster overall for the RTQP component than it is for the PI
OLEDB enterprise component. The RTQP version has a direct connection to the PI FD database on the SQL
server hosting the AF back-end database whereas PI OLEDB enterprise at its client side does not
have this direct connection. The second item in play is the paging of the results. Since the
execution is done server side and the client is simply receiving the result set as soon as some
of the results are ready they can be sent over to the client which can display them immediately.
That's why we were able to see results within half of a second whereas in PI OLEDB enterprise
the entire results that had to be returned to the client before the result set could be assembled.
This means that the first page of results is not available until the entire query is executed.
Let's add an order by clause to both queries. This order by clause forces the entire result
set to be known by the query engine prior to the ordering to take place which means that
the entire ordering has to occur before the first page results can be sent to
the client. Let's rerun this query and see how much longer it takes to see our first results.
The first page was substantially slower to be received however it was still a fast execution
in general. Now let's do the same thing with PI OLEDB enterprise. Remember from last time that it
took 21 and a half seconds to get the first result and almost 25 seconds total to get all of them.
If we look at the execution times again we see 21.6 seconds for the first rep and 25 seconds
for the total this is not much different from the other query without the order by
clause since the overall query execution plan has not changed much. In both cases
the entire results set had to be returned to the client and in this case they were simply
ordered by the unit of measure. It's not much of a difference and so we don't see much of a
difference in the time it takes to receive the first result. Then the overall time to receive
the entire result set is also not much of a difference. Hopefully this drives home
the idea of what's going on behind the scenes in the different query engines.
Next let's concentrate on the idea of sending pages of results back to the client. To do this
let's get rid of the order by and select just the top 100. This is a substantial decrease in the
number of results where we're going from three hundred seventy seven thousand down to just one
hundred. As we execute this query let's keep in mind that it took twenty one point six seconds
to get the first row and 25 seconds total. Now let's look at the execution time. We see there's
a little bit faster at eighteen point three seconds and a total time was almost immediately
after that as it only needed to return 100 rows. It still took a very long time to return these
100 rows because again the query execution is occurring client-side so the client has to know
the entire result set prior to figuring out which are the top 100 even though they're not ordered.
Now let's compare this to the same query in the RTQP version. Remember in the ordered
version this took almost three seconds to get the first row and just over seventy seconds seconds
to get the entire result set. Let's execute this query now let's look at the execution
time it took just over a third of a second to receive all the rows we were interested in.
Now this is admittedly a contrived query in the fact that we have only 100 rows being returned and
we're not in charge of the ordering. In general this isn't a very desirable PI SQL query because
we're not really in control which 100 results the query is returning however this query really shows
the differences between the RTQP engine and the PI OLEDB enterprise engine. The differences in the
execution time is as I mentioned before the RTQP engine is returning the results to the client as
they're ready since this is not an aggregated or an ordered query we do not need to know the entire
result said prior to returning just the 100 rows and since we're able to return pages of results
as they're ready to the client we're getting a substantially faster execution. In fact in this
specific query the RTQP query engine is 50 times faster than the PI OLEDB enterprise query engine.
Now the last thing I'd like to show is the differences in the machine resource consumption
for the client application for both RTQP and PI OLEDB enterprise. Let's return the query
to the original state and let's open up Windows performance monitor. Let's renew display and let's
go to the process tab speaking on information about the SQL commander lite process. Let's add
processor time; as well as private bytes and let's click OK. Let's change private bytes to something
a little bit more readable and add a couple zeros. Now let's run the query for our RTQP.
Query's finished and we see a jump in the private bytes which represents the
memory usage for the PI SQL commander lite process as well as percent processor time.
Let's run the PI OLEDB enterprise query.
Here we see the memory consumption during the RTQP execution and the memory consumption during
the PI OLEDB enterprise query execution. We also see the % processor time going
way up just for a short amount of time for RTQP versus a longer amount of time for the
PI OLEDB enterprise execution. As we can see there's a substantially higher amount
of machine resources being consumed in the execution of the PI OLEDB enterprise query
versus that of the RTQP query. This is again due to the client-side execution of PI OLEDB
enterprise versus a server-side execution of RTQP. Now in this environment the client
machine is also the server machine so we're not removing any resources from the machine itself,
however in your environment if you're using a different machine for the client application
as you are for any of your servers, then the concept of moving the machine resources off of
the client machine and onto the server might show you an actual noticeable difference.
Finally one thing not shown here since it is all in one machine is in network resources
since only the results set is being returned the network resources are substantially lower
for the RTQP version as they are for the PI OLEDB enterprise version which is using the
AF SDK to return all the results and then building the results at client side. Not
only will you notice a reduction in machine resources but also in network bandwidth usage.