3 Replies Latest reply on Aug 9, 2018 9:56 PM by jking

    How do linked table parameters work?

    arosenthal

      I have a question about how linked table parameters work internally. I have several tables that query external Oracle databases for data, and these tables are then referenced with Table Lookup Data References (TLDRs) on element attributes. As far as I can see, the TLDRs can select rows from the tables in one of two ways:

       

      a) using a WHERE clause in the configuration

      b) using table parameters

       

      Up until now I have been using the first approach, but I am interested to learn more about the second. If I adopt table parameters, how does the linked table query get executed on the server? I think there are two possible behaviors:

       

      1) The linked table is executed once without the parameter in place. All the table data would then be cached internally and the TLDR would then select from this cache by substituting in the appropriate parameter value.

      2) The linked table is executed multiple times for every TLDR, with the appropriate parameter value substituted in.

       

      I want to try to avoid the second behavior because my concern is it would create too many query executions, since I have so many attributes with TLDRs.

       

      Does anyone have any insight into this?

        • Re: How do linked table parameters work?
          David Hearn

          Table parameters are typically used when the data from the linked table would be very large and only a small portion of the actual table is required. When using table parameters, a cached copy of the portion of the table is cached in memory for each unique set of parameters. Based upon your concerns, you are better off not using table parameters in your case.

            • Re: How do linked table parameters work?
              roblyon9

              Hi,

              I see this is a year old but I have a clarifying question with respect to this.

              If multiple elements/attributes use the same parameters, then is the table still only cached once for those parameters, or a separate copy for each attribute?

              And where does the cache live: On the AF server or on the application (e.g. PI WEB API server or PI Vision or System Explorer client or Processbook)?

              We want to stay flexible but prevent too many calls to the particular database.

               

              Thanks in advance.