06-05-2017 02:16 PM
Hi,
When performing batch indexing in the unity client it always is taking >1 minute to navigate between batches. I have noticed that this query is being run each time and it seems to be the cause of the slowness. It appears to always return 0 results. I have checked and stats are up to date on both tables and the indexes are within allowable fragmentation limits - any other options to speed this up?
SELECT tbl1.batchnum "1_batchnum" FROM (hsi.archivedqueue tbl1 LEFT OUTER JOIN hsi.itemdata tbl2 ON tbl1.batchnum = tbl2.batchnum) WHERE tbl2.batchnum IS NULL
82 seconds to execute - 1,760,402 reads
Thanks
Anthony
06-09-2017 01:33 PM
Appears to be related to this SCR:
Short Description: When a large number of empty batches exist in a database logging into OnBase Studio or loading the Batch Scanning/Batch Processing layouts may fail or take a significant amount of time to load when attempting to clean up the records of those empty batches
SCR #: 274957
Status: Closed-Completed
Approved Version: 16.0.2
Business Problem: When a large number of empty batches ( batches that have an entry in the hsi.archivedqueue table but no corresponding document entry in the hsi.itemdata table ) exist in a database logging into OnBase Studio may timeout and fail as Studio is attempting to clean up the records of these empty batches in the archivedqueue table. There also may be performance issues when the Batch Scanning and Batch Processing layouts are loaded in Unity Client. We run the same service method to clean up these empty batches there and a large number of batches can cause this to fail and display the same exception in both places 'Failed to Remove Orphans from Custom Processes' in Diagnostics Console. This causes delay in loading the Batch Scanning/Batch Processing layout as well.
Functionality: Capture Process Designer | Unity
06-15-2017 01:55 PM
Hi,
Anthony is correct. The query was first implemented in OnBase 16.
SCR 274957 was created and completed to remove it from automatic execution during user work in Unity Document Imaging. The query execution will likely be slow for any system that has a large number of documents and a large number of batches as it will, at minimum, index scan both itemdata and archivedqueue.
The fix for this issue is available in 16.0.2.81 and higher.
Ansley
06-20-2017 02:06 PM
06-21-2017 10:22 AM
06-21-2017 11:54 AM
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.