OnBase 12 Keyword Loading Improvements
In OnBase 12 we have improved how keywords are loaded. The two major changes include:
- Being able to load multiple keyword type values in one query
- Querying for only the specific keywords needed
Neither of these changes will affect how you need to configure or use your OnBase system, but are enhancements in how keywords are handled by the Application Server to improve performance and scalability. These changes only apply to standard keyword and modules that use the .NET core.
What does this change mean for keyword type groups? Nothing. Keyword type groups are still recommended for performance reasons as the data is contained in one table and will be more efficient on the database server. They will continue to be a good option when needing to query for many keyword types.
What does this change mean for keyword type groups? Nothing. Keyword type groups are still recommended for performance reasons as the data is contained in one table and will be more efficient on the database server. They will continue to be a good option when needing to query for many keyword types.
The Old Way
In versions of OnBase prior to 12 when we needed to work with a keyword on a document we would have to load up all the keyword types on that document type. This would require a separate database query for each keyword type. So let’s say you have a Workflow that is working with one of the keywords on a document, but there are 100 standard keyword types configured for that document type. When that one keyword is needed, we run queries to get all 100 keywords. The same would apply if you were to view the keywords on the document.
You would see something similar to this in Diagnostics Console:
SELECT * FROM hsi.keyitem101 WHERE itemnum = 1234567
SELECT * FROM hsi.keyitem102 WHERE itemnum = 1234567
SELECT * FROM hsi.keyitem103 WHERE itemnum = 1234567
SELECT * FROM hsi.keyitem104 WHERE itemnum = 1234567
..........
SELECT * FROM hsi.keyitem200 WHERE itemnum = 1234567
SELECT * FROM hsi.keyitem201 WHERE itemnum = 1234567
We have found that in many cases only one or a few keywords need to be loaded, thus prompted a change in how we query for them.
You would see something similar to this in Diagnostics Console:
SELECT * FROM hsi.keyitem101 WHERE itemnum = 1234567
SELECT * FROM hsi.keyitem102 WHERE itemnum = 1234567
SELECT * FROM hsi.keyitem103 WHERE itemnum = 1234567
SELECT * FROM hsi.keyitem104 WHERE itemnum = 1234567
..........
SELECT * FROM hsi.keyitem200 WHERE itemnum = 1234567
SELECT * FROM hsi.keyitem201 WHERE itemnum = 1234567
We have found that in many cases only one or a few keywords need to be loaded, thus prompted a change in how we query for them.
Load Multiple Keywords in a Single Query
The first big change for OnBase 12 is to load keyword values in a single query instead of one per keyword type.
If all of the keywords on a document needed to be loaded, you will see now is something similar to:
SELECT 101 as ktid, Tbl.keyvaluechar as keyalpha, NULL as keynum, keysetnum
FROM hsi.keyxitem101 XTbl, hsi.keytable101 Tbl
WHERE XTbl.itemnum = 1234567 and XTbl.keywordnum = Tbl.keywordnum
UNION ALL
SELECT 102 as ktid, keyvaluechar as keyalpha, NULL as keynum, keysetnum
FROM hsi.keyitem102
WHERE itemnum = 1234567
..........
UNION ALL
SELECT 201 as ktid, NULL as keyalpha, keyvaluesmall as keynum, keysetnum
FROM hsi.keyitem201
WHERE itemnum = 1234567
The new query may not be pretty, but is faster to execute and takes fewer trips between the Application Server and database. It will look different based on the data type of the keywords, but you get the idea.
Here we have some tests that were run comparing 11.0 and 12 Unity Client viewing document keywords from a hit list. As you can see, we were able to drastically speed up the load time.
Note: Using an excessive amount of keywords on a single document is NOT recommended and was done for testing purposes only.
If all of the keywords on a document needed to be loaded, you will see now is something similar to:
SELECT 101 as ktid, Tbl.keyvaluechar as keyalpha, NULL as keynum, keysetnum
FROM hsi.keyxitem101 XTbl, hsi.keytable101 Tbl
WHERE XTbl.itemnum = 1234567 and XTbl.keywordnum = Tbl.keywordnum
UNION ALL
SELECT 102 as ktid, keyvaluechar as keyalpha, NULL as keynum, keysetnum
FROM hsi.keyitem102
WHERE itemnum = 1234567
..........
UNION ALL
SELECT 201 as ktid, NULL as keyalpha, keyvaluesmall as keynum, keysetnum
FROM hsi.keyitem201
WHERE itemnum = 1234567
The new query may not be pretty, but is faster to execute and takes fewer trips between the Application Server and database. It will look different based on the data type of the keywords, but you get the idea.
Here we have some tests that were run comparing 11.0 and 12 Unity Client viewing document keywords from a hit list. As you can see, we were able to drastically speed up the load time.
Note: Using an excessive amount of keywords on a single document is NOT recommended and was done for testing purposes only.
Query for Only the Specified Keyword
The other big change was the ability to only load a specific keyword or subset of keywords. Take the Workflow example again. Since the action is setup to only use one keyword type, we can now load up that one keyword in a single database query.
SELECT * FROM hsi.keyitem150 WHERE itemnum = 1234567
Another example is a Workflow that would replace one keyword on 100 related documents and each of those documents had 70 keyword types. To replace a keyword on a document (In 11.0 and earlier), we first have to get their current values. A little math (100 documents x 70 keyword types) tells us that 7000 queries would be needed to load up all these keywords. Using OnBase 12 we were able reduce they keyword queries down to 100 (100 documents x 1 keyword type) for the same action!
SELECT * FROM hsi.keyitem150 WHERE itemnum = 1234567
Another example is a Workflow that would replace one keyword on 100 related documents and each of those documents had 70 keyword types. To replace a keyword on a document (In 11.0 and earlier), we first have to get their current values. A little math (100 documents x 70 keyword types) tells us that 7000 queries would be needed to load up all these keywords. Using OnBase 12 we were able reduce they keyword queries down to 100 (100 documents x 1 keyword type) for the same action!
