1 of 1 people found this helpful
AF Table functionality is not really designed for storing large databases. An old rule of thumb is that if your table is larger than 5000-10000 rows, it probably doesn't belong as an AF Table. However, as of AF 2.6, AF Tables can pass through query parameters to SQL, so sometimes it's possible to work with tables that very large in the SQL backend which are greatly reduced in size during your query (e.g. by time range, asset, etc.). It's worth benchmarking, while keeping an eye on just how much tabular data has to be shipped to the client when you're querying for table data. This approach implies a linked table, not imported.
If that doesn't deliver the performance you're looking for, you might consider historizing the data in PI. Data duplication is rarely a desirable thing, but cases like this are a reason to consider it. The PI RDBMS Interface can read from SQL and historize the data in PI, where time-series queries can be much faster.
- Table lookup data references are primarily intended for configuration items and smaller real-time data sets. Linking an AF table to a large external database and using table lookup data references against it can cause slow performance. It is a best practice to keep table lookup data references against configuration items and smaller real-time data sets (10,000 rows or less).
- If the external data that needs to be linked or imported to a PI AF table cannot be reduced to less than 10,000 rows by the query, it is recommended to consider bringing the data into the PI Server with the PI-RDBMS interface or PI OLEDB COM Connector.
Thanks for the response.
I have tried the parameters approach and it looks quite good (the table is now 1 Million records, and the performance is acceptable).
I ran into one thing that I could not solve.
I use the element name as the key to the table (in MS SQL Server). That key is an int in the database. Once I configured the parameter with a default value of %Element%, I got an error message that the varchar could not be coerced to an int. Strangely enough if I do that in the template (this was before the query parameters) the conversion is silent and successful. Why is this behaviour different with pass through parameters?
One way to solve this is to add an attribute of type int that outputs the name of the element. I tried that with a Formula but I could not make that happen.
So I ended up with changing the data type to varchar(10) in the SQL table. I would like to be able to use an int though, because that will definitely be faster.
The main purpose of AF Tables is to use it with the Table Lookup DR. With that said, %Element% is a substitution parameter that the Table Lookup DR will resolve before passing the value to the AF Table for querying the SQL Database can recognize. If you directly specify %Element% in the AF Table configuration, you will get the error below because %Element% is of type nvarchar.
The way you should do it is to leave the default value for the parameter blank and specify %Element% in the config string of your Table Lookup DR. You do not have to change the data type in the SQL Table.
My element name is '2'. So my attribute value is 'eugene'
That works. Thanks.