3 Replies Latest reply on Jun 14, 2013 5:09 PM by Asle Frantzen

    AFTables and memory usage. Any differences with 1 big query vs. several smaller ones?

    Asle Frantzen

      We're depending heavily on AFTables to integrate one of our clients' MES system with PI AF, and being that we've already done a few things to decrease the loading time of their ProcessBook displays I would like to know if there is any performance difference (or memory usage differences) with keeping one large table with metadata for multiple types of equipment, compared to splitting it up into different tables with less data in each.


      My current case involves a ~5000 rows query, where they're distributed 3500/1500 for two different types of equipment.

        • Re: AFTables and memory usage. Any differences with 1 big query vs. several smaller ones?
          Rick Davin

          I have a similar situation but rather than wait for an informed answer, I went ahead and split them up into individual views per equipment.  I reasoned there would be small overhead for each table defined, but that later when I am on an equipment element that its table lookups would be improved by searching through a smaller subset.  I also found it easier for me to visually/manually review the subsets - easier to find a needle in a small haystack than a very large haystack.  We are eagerly looking forward to AF 2.6 for pass-thru queries.


          That said, I like your question and look forward to hearing an official answer.

            • Re: AFTables and memory usage. Any differences with 1 big query vs. several smaller ones?

              You will likely be dissatisfied with my official answer, which is - it depends.  AFTables utilize .NET DataTables and OLEDB connections to retrieve data.  If you split a large table into multiple tables, you will increase the amount of round trips to retrieve all the data, as well as increase the amount of memory because the AFTable and .NET Table columns have to be defined more times.  On the other hand, If you don't need the full set of table data to display your processbook display, you can achieve faster access to get to the subset of table data you need by not loading data you are not going to use.  


              If were talking two tables vs one table, the difference will be insignificant in terms of memory - although performance could be affected if your MES system response time is particularly slow and you need both tables. If we are talking 1 table vs 1000 tables, then experimentation is in order.  


              Unfortunately, there is no single answer.  The latency and response time to your MES system, width of  your tables, how much of the data you will need at load time, are all things that factor into the equation.  To get your answer, you will need to test your system and make those tradeoffs decisions yourself.