[Imported] Search Keys -- Good or Bad?

[sbeadle]

>>Message imported from previous forum - Category:ClearSCADA Software<<
User: tfranklin, originally posted: 2019-09-30 16:26:51 Id:528
I'm looking to start some discussion on the topic of Search Keys. They seem really beneficial when used properly. That said, we've never really used them in the past so my exposure to them is fairly limited. From what I can tell, a search key is a new metadata field that acts (when Tag is checked) a value map. Search keys are required to be unique if Tag is checked, so I'm going to assume they're indexed. With tag checked, you get some new liberties with expressions where instead of full path you can use searchkeyhere.propertyhere. Pretty neat stuff.

For those who have insights into Search Keys, I ask the following:

1. What're the negative impacts of search keys? Ex: Are they treated like indirect animations where they should really be avoided unless used wisely? Do they cause excess CPU strain?
2. Do search keys impact client load times?
3. What are some clever ways you've seen search keys used?
4. What are some really bad ways you've seen search keys used?
5. Why would anybody use search keys with tag unchecked?
6. Is there an advantage to having a search key with tag unchecked vs a normal string metadata field for Config/Data?

[sbeadle · Accepted solution]

>>Responses imported from previous forum

Reply From User: adamwoodland, posted: 2019-09-30 22:28:00
Search keys are the original metadata, the standard metadata fields only came into the product about 12 years ago based on the limitations of search keys.

They're great when used correctly, basically without the tag they're an indexed lookup, with the tag ticked they're a unique indexed lookup. What that means is SQL queries that use them in there WHERE clause are more efficient that those with a standard string metadata field (which isn't indexed).

To answer your questions:

1. Search keys are only stored in the config stream, and as they have an index they use more CPU effort when updating. What this means really is if the content of the fields are changing 'often' then search keys are probably not the best option (compared to say standard string metadata fields against the Data stream). One of the whole keeping Config and Data streams separate was to minimize the data flushed to disk with each point value update, and back with ClearSCADA on NT4 days the disk impacts was significant. Now days disks access is so much faster the actually impact of Data and Config streams on disk is minimal but still needs to be considered for other things like network bandwidth.

2. Depends what you mean by client load times. The indexed efficiencies of search keys should mean SQL queries are quicker for clients to retrieve from the server so the perceived performance should improve

3. Common uses are for say the old tag names from imported systems, i.e. ClearSCADA now has its full OPC name (Group.Group.Group.Object) whereas the tag option allows an alternative mapping of names, i.e. P123SN23 (which may be what old systems may still use to access data via OPC-DA). Not sure of any specifically clever ways, it is all about planning how your SQL queries will query and planning your metadata accordingly

4. Anything that is frequently updated. As mentioned above any really old ClearSCADA systems will likely all be search keys, and so those are bound to have some sub-optimal setup (by current standards) solutions there. Can't think of anything specifically though.

5. When tag is checked you're forcing the content for that search key to be unique per object, that has useful cases but also means you couldn't have some grouping. For example you may store in the search key some common attribute across many parts of the database, an example could be the 'owner' of the asset, and different owners due to maybe contractual or commercial reasons could be dotted across the database. Using a search key could allow a much more efficient search to be done. On a database of a few thousand objects then a table scan rather than a indexed lookup is probably a second or so difference, but when you're dealing with databases of 200,000 to a million objects the impact is significant. Last time I checked I believe you can only have one search key set as tag, so other search keys have to have that option untagged.

6. See above, search keys are indexed, so SQLs using those fields are much quicker. When you start using large databases this becomes very important. But using them at the wrong time on a large database may also cause you more problems

Reply From User: tfranklin, posted: 2019-09-30 23:08:48
[at]adamwoodland very interesting stuff! I can definitely see some benefits to moving a few fields we utilize over to search keys. I'll put some thought into this and see what I can break.

One quick question -- I was browsing the help file and noticed an article for Historic Search Keys. In the 10 minutes I spent clicking around, I couldn't find a way to actually implement one and get it to show up as mentioned in help. Any ideas on what those are vs a normal search key, or how to implement one?

Reply From User: adamwoodland, posted: 2019-09-30 23:47:53
Unfortunately its not a case of a simple change, you'll have to create a new field, copy things over between fields (including setting property overrides as necessary) and then deleting the old field and renaming... frankly a pain and something you probably only really want to do if you are having performance issues with SQL. A dodgy hack of the metadata XML file with a cold restart never used to work and I assume still won't 🙂

I wasn't aware of the term historic search keys so I looked it up. Looks to be the same as search keys, just if you also enable historic (so limited to points) you can see them under search keys in the "OPC Historic" toolbar (much the same as you can see them in the "OPC Data" toolbar under search keys)

Reply From User: tfranklin, posted: 2019-10-01 00:51:57
Completely agree. I'm just considering it all for new builds and making them more efficient. I've converted fields in the past from one data type to another and succeeded in blowing up the server. Lessons were learned. It has been some years but it was indeed a dodgy hack and converting a string to a reference field. I'd rate the experience as a 0/10, do not recommend.

Reply From User: andrewscott, posted: 2019-10-01 08:45:10
The January 2019 monthly updates (for 6.78, 6.79 and 6.80) contain fixes for several problems with converting metadata fields between different types, including converting string fields (containing object names) to reference fields.