Slow running query while using Unity batch processing

[Anthony_Boyd]

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

[Thomas_Reu]

My bad must have been Monday.  The query is looking for records in the archivedqueue table that don't have a corresponding record in the itemdata table.  For me this returns 599 rows really quickly.  That said, if your query returns an exceptionally large number of records I could see that this might slow you down.  This needs to go through your FLOS to Hyland,  Hopefully they can explain what the check is supposed to do or perhaps you have something setup incorrectly in your environment.  If your setup is perfect and everything is performing as designed then that only leaves a sql code change which also has to be Hyland.  I'm guessing blindly, but any of the following options would reduce the number of records returned....

1.  Clean up the archived queue table.  Fewer archived records, will prevent a return of a large volume of records

2.  Add at least 1 corresponding record back to itemdata - which would seem to defeat the point of archiving (definitely would not recommend)

3.  Add a date check to the sql where statement, so it doesn't search over the entire population of records, but maybe just the last year's worth (really depends on your record volume)

[Anthony_Boyd]

I'm right there with you, I have never seen it NULL (although for the 1st time today I did see this query return results while testing). I'm not sure why the Unity client is issuing this query.

[Anthony_Boyd · Accepted solution]

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 

[Thomas_Reu]

This is good to know thanks for posting