Deleting a version is slow
Article ID: 424369
Updated On:
Products
Issue/Introduction
After upgrading to Harvest v14.5.01, when I execute a "Delete Version" process, it takes almost 30 minutes to complete. Before, it was able to complete in seconds.
With Harvest logging set to level 5, we reproduced the problem, examined the resulting HServer log file and found a particular EXECUTE_DELETE_VERSION transaction of concern:
<date> 18:02:01 | Start EXECUTE_DELETE_VERSION /pt_HClient://<client hostname>/<client pid>
...
<date> 18:28:38 | End EXECUTE_DELETE_VERSION TRANSACTION TIME: 0
Looking inside that transaction we found the SQL query accounting for the most time is this one:
-- START: <date> 18:02:02 DB Connect#: #### SINCE LAST SQL: 0
delete
from harversiondata
where refcounter = 0
-- SQLFreeStmt(SQL_CLOSE)
-- RETURN CODE: DB_NOMORE_DATA
-- ROWS UPDATED: 0
-- END: <date> 18:28:38 **** SQL Milliseconds: 1595262
Environment
Harvest Software Change Manager v14.5.01
with Oracle database
Cause
The performance issue with the "Delete Version" process is occurring because of a new SQL statement introduced in the improved DELETE_VERSION transaction.
Resolution
To resolve this, please add the following index to the database by connecting to your Harvest schema and running the command below:
CREATE INDEX HARVERSIONDATA_REFCOUNTER_IDX
ON HARVERSIONDATA(REFCOUNTER)
TABLESPACE HARVESTINDEX;
If you are using a custom name for the Harvest Index Tablespace instead of the default "HARVESTINDEX", please substitute it in the command above.
Additional Information
More information about locating the Harvest log files can be found here: Location of the Harvest log files
More information about how to increase the logging level for Harvest can be found here: Increase Harvest Log Levels
Feedback
