I do not believe using <None> data reference is good for much.
When you use Lookup Table, the table is cached onto AF Clients memory. All queries then go to the local cache. Therefore I believe that the action of lookup table will be most of the times faster than executing SQL statement each time.
Please see below KB for more details:
Jinmo, Thank you for the reply. We are using imported not linked tables, and the asset counts we are talking about are around 6,000. So we do see a delay with bringing all that in to cache. If I can state the question again, which applies to large asset counts, which is faster?
(1) Attributes that are static, i.e. - using <None> for reference type;
(2) Attributes from "imports" and then table lookups - btw the "linked" option using parameters and getting data directly from external tables was extremely slow!; and
(3) PI Point references.
I was speculating about a few things:
(1) The attributes with <None> have values/timestamps that are coming directly from PI AF, i.e. - no hopping around to linked tables - there are 5 of these type attributes and some of these are String Builder and use attribute values from the table lookups;
(2) The attributes with Table Lookup references, are getting data from PI AF too, via the "imported" tables, each requires a SQL statement for each element/attributes - there are 55 of these from three different tables;
(3) the PI Point referenced attributes are getting current values/timestamp, so that seems quick enough - in my use case I have 55 of these.
I am very careful that no lookup table has more than 8k records by splitting those lookup tables up. So the initial load of cache has all three tasks to perform to build up the cache of element/attributes for the client -
(1) Get <None> reference values; and
(2) Get the table lookup values.
(3) Get PI Point reference values;
My theory is that if leave the PI Point references alone but remove any table lookup references and instead use <None> and populate the values with a service, only if they have changed, that the client cache load will improve, and I hope, significantly.
I think 1) is fastest is most cases, because the data is loaded when the element and all it's attributes are resolved. And you need to do that to look at any data.
2) hits the cached linked table, but it introduces the overhead of performing the lookup
3) i expect to be the slowest, as you need a round-trip to the PI Server for that.
My point-of-view is that i choose PI Points for historical data, linked tables when the management of the data lies outside of the management of the AF model, and static variables is there is a process to update the AF model that can include the maintenance of the static values. You see that my reasoning is more based on maintenance effort and not performance. Performance requirements you can just buy your way out in hardware in most cases, but maintenance is costly and anythink making that easier and less sensitive for errors is better in the long run.
I agree with Roger. 1) is the fastest. When you are doing a search the search will be most efficient if all of the work is done by the AF Server and not by the AF SDK Client. Data references are evaluated by the AF SDK Client when you are doing a search and <none> by the server. I would expect your client load times to improve but with the cost of introducing more complexity into the system. The AF SDK Client will cache the object values, until you call refresh on the object or the collection that owns the attributes.
Also, if the AF Attribute data is changing often, then you want to use data references (PI Point, Table Lookup). AF Attributes with <None> are not ideal for frequent changes as well as for holding values with Time-stamps. How often will this data change?
Thank you for the reply.
We have done all the research with these attributes and whether we want history and frequency of data changing - I think that 90% of these won't ever change, they are like "Master Data"; the rest may change, but only like once a month. Anything that is changing often like a status, etc., is a PI Point. About the only thing that I don't like on this idea is that the <None> reference types always have a 1970-01-01 timestamp and that is annoying.
Not nessecarily 1-1-1970. if you create a new version of the element and set the effective date, the date of the <None> reference types will change too. Or don't use versioning at all but just set the effective date.
Do do extensive testing though! I do not fully know what the effects are. Basically you are indicating the element did not exist before the timestamp set. Not sure which clients deal in what way with effective dates.
I would still recommend a time-series lookup table. Don't know how many lookups you do and how often, but i did not have any performance issues so far on 10K element schemas with many lookups on large tables. You do need to set the caching properly though.
Agree with Jimmo here. None only to be used for nearly static values. If the data is written, the load is moved to SQLserver to persist the value change.
Properly setting the cache timeout can make a MAJOR difference, so be sure to set that to a reasonable level.
Thank you for replying, can you please read my reply to Jimno and comment?