4 of 4 people found this helpful
I did a similar project a few years back, where the customer wanted to integrate their LIMS via AF. We used a linked table (via a view) that brought in approx 10K rows of data, that were then queried using the Table Lookup data reference. This dataset represented values for every sample point This did not appear to hit the server with a significant increase in requests. We specifically limited the number of rows in the AF Table, due to the potential for performance impacts with a large row count (see this thread for some more information). You will find a number of threads in these forums that talk about performance of AF tables with large row counts. The attribute lookup via the Table Lookup data reference hit the locally cached data in the AF Table, which was periodically refreshed at a pre-defined interval.
My experience with this was that at the end of the day, you can configure the table lookup to the LIMS database a number of different ways to access the data, and each will have their relative strengths and weaknesses. In our case, it was that a limited amount of sample history would be available in the linked table due to our requirement to maintain an upper limit of 10K data rows (which was an arbitrary limit). With some creativity, you can probably write a stored procedure that takes in a list of components / sample points, and return the required results from a single query, or even re-use the query that your .Net utility executes.
2 of 2 people found this helpful
Thanks for the info, John.
I just had a few follow up items.
1. For those interested in more information on AF Table caching, here is an article:
2. To avoid the problem you mentioned of querying large row sets with the Table Lookup Data Reference, query parameters were added to AF (2.6, I think). Here is the documentation:
With this method, we can affectively request a small number of rows (often just one row) per attribute from the foreign table rather than having to return thousands of rows from the foreign system with our AF table query and then query this large result set with the table lookup DR. So I guess some testing would be needed to see if using a view on the foreign system that gives a larger cached AF table is better or worse than using query parameters to cache individual results for many attributes.
Emad, please let us know what you find to work for you.
1 of 1 people found this helpful
Yes, I remember when the ability to create parameterised queries for AF linked tables was released, and in some instances this goes a long way to solving some cases where otherwise a large dataset would have needed to be returned. I think in Emad's situation, this could still result in many queries being made to the Oracle backend, as each component mapped to an Attribute would pass the component name or ID as a parameter to the query. If there is concern with the number of queries to the database that could be executed, I'm not sure if query parameters in the table link would address that concern. But again, creative stored procedures or queries might just do it! Agree that testing to see what is the better option is the way to go.
Thanks John,this is exactly what I wanted to say. How do you think a stored procedure would fix this multi query issue? The problem still exists as the parameters values will change for every component_name. On the other hand, I believe I'll go with linked tables as I don't see value of duplication a large data set between Oracle and AF SQL tables specially that its a huge one.