4 Replies Latest reply on Aug 17, 2015 7:47 AM by JanvanderVen

    Link or import a table with ~20 million records

    JanvanderVen

      Hi all,

       

      I have this table containing daily data for 10 years and about 5000 points.

      A quick calculation shows that this is ~18 million records. And it is meant to grow.

      I want to use it in AF.

      What would give me the best performance: a link or an import?

      As said before, I also need to be able to add records through the AFSDK.

       

      Please advise.

       

       

      Kind regards,

       

      Jan

        • Re: Link or import a table with ~20 million records
          asorokina

          Hi Jan,

           

          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.

           

          Also you can find some hints in Scott Robertson's PI AF Templates - White Paper

           

          • 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.
          1 of 1 people found this helpful
            • Re: Link or import a table with ~20 million records
              JanvanderVen

              Dear Anna,

               

               

              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.

               

              Kind regards,

               

               

              Jan