Hyland Connect

Anthony_Boyd · ‎06-05-2017

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

Anthony_Boyd · ‎06-09-2017

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

View answer in original post

Ansley_Ingram · ‎06-15-2017

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

Anthony_Boyd · ‎06-20-2017

Just wondering, does this cleanup still occur during Studio login or is it done elsewhere now?

Ansley_Ingram · ‎06-21-2017

Anthony,

The SCR was to remove the automatic execution of the cleanup from the product entirely. You shouldn't see it in the Unity Client or in Studio in 16.0.2.81 and higher.

Ansley

Anthony_Boyd · ‎06-21-2017

Awesome, Thanks Ansley!

Hyland Connect

Slow running query while using Unity batch processing