RTQP Performance Considerations

Document created by kduffy on Sep 25, 2018Last modified by kduffy on Aug 7, 2020
Version 8Show Document
  • View in full screen mode

The first release of the RTQP Query Engine and PI SQL Client have very few performance considerations to keep in mind when writing queries.

 

With PI OLEDB Enterprise being a client-side bandwidth-optimized query engine separated from the AF Server's backend PIFD SQL database by the AFSDK, there were numerous performance considerations to keep in mind while developing queries. Those were laid out in the PI OLEDB Enterprise Query Optimization White Paper, and as seen in Optimize PI OLEDB Enterprise query performance, small changes to the query can have large performance implications.

 

With the RTQP Query Engine being server-side with direct access to the AF Server's PIFD SQL backend, the queries are executed by a cost-based optimizer than will handle the performance considerations automatically. There are currently no ways to override the execution plan developed by the query optimizer, but we are open to feedback on this concept and will consider implementing it in a future release if the use case presents itself.

 

With that being said, there is one performance pitfall in the RTQP Query Engine that should be avoid, and that is using 'SELECT *'. Since the table in the RTQP schema are denormalized, the data returned can be quite expansive. For example, in the Master.Element.Attribute table, the snapshot value of each attribute will be outputted with SELECT *. This can be a very expensive operation if the value is not needed, but SELECT * was used for easier query writing. As we see in this query execution output, the SELECT * version took just under a second to execute (the fact that all attributes point to sinusoid results in a fast query):

 

But if we only output some of the metadata for the attribute, we have a much faster query execution time:

 

On a large scale, this can be very impactful. For example, the Windfarm database seen in Not authorized to view the specified discussion 2665 has an, expectedly, much larger gap in performance:

 

The more expensive the snapshot value operation, the larger the difference with table-lookup data references, rollups, and analysis data references being the most impactful.

 

Ultimately, the RTQP Query Engine itself is automatically handling the performance tuning on your behalf, but it will return all the data that is actually requested in the query. So the best way to keep the queries as performant as possible is to only request the data that is needed.

1 person found this helpful

Attachments

    Outcomes