I'm trying to improve the performance of batch processes (which basically "adjust" hierarchy records in a folder structure, RBAR). The batch process does lots (tens of thousands) of updates like this to adjust the hierarchy after each folder change. (I'm calling it "folder", as that's what the users see, but I guess Autonomy speak is Project given the table name)
DECLARE @P1 datetime = '20150826 20:06:58'
, @P2 int = 1
, @P3 int = 3469
UPDATE MHGROUP.PROJECTS
SET LEFT_VISIT = LEFT_VISIT - 2
, EDITWHEN = @P1
WHERE TREE_ID = @P2
AND LEFT_VISIT > @P3
The PKey (which is non-clustered) on MHGROUP.PROJECTS is [PRJ_ID]
The clustered index key is TREE_ID, LEFT_VISIT, RIGHT_VISIT
There are a total of 13 indexes on the table ... so having these batch updates modify [LEFT_VISIT], and because that is a key in the clustered index, means that all the associated index records have to be updated. On each of the tends-of-thousands of single single-row updates like this one.
If I just change the Clustered Index and make it Non-Clustered, and make something else (.e.g the primary key) the Clustered index is there any consequence (to the APP)? or is it just the time to make the clustered index change and then see whether it improves anything?
Or am I barking up the wrong tree?