update table execution takes too long when MVCC=true
Manik Dhawan
I have a table with 18 columns with a primary key and 8 indexes. If I make a usual connection to H2 DB (embedded mode) and update the non-index field of this table, it takes around 20 seconds in H2 console to update 50000 records. However, if I set MVCC=true in connection string and then try to update SAME 50000 records, the table does not get updated for even more than 30 minutes. I am call this update statements from H2 console here.
Schema below
CREATE TABLE TEMP ( SWITCHIPADDRESS VARCHAR(16), ID BIGINT NOT NULL IDENTITY, MACADDRESS VARCHAR(14), USERID VARCHAR(32), TIMESTMP TIMESTAMP NOT NULL, LINKCOUNT INTEGER, HASLINKTOSWITCH BOOLEAN, LINKIPADDR VARCHAR(16), IFINDEX INTEGER, PORT INTEGER, SLOT INTEGER, VLANID INTEGER, IFSPEED INTEGER, IFADMINSTATUS INTEGER, PORTDUPLEXMODE INTEGER, UNP VARCHAR(32), DOMAIN INTEGER, DISPOSITION INTEGER, PRIMARY KEY (ID) )
Indexes
KEY ForwardIdx (SwitchIPAddress,MACAddress,slot,port), KEY ForwardSwIPIdx(SwitchIPAddress), KEY ForwardMACIdx (MACAddress), KEY ForwardSlotIdx (slot), KEYForwardPortIdx (port), KEY ForwardVlanIdx (VlanID), KEY UserIdIdx (UserId), KEYUNPIdx (UNP)
I can see in the trace log file that thousands of keys are first getting removed and then getting added which is probably taking time. But I wonder, why would key realignment required when what is being done is a simple update on non-idx field.The problem remains even if I have just 1 index which is used in where clause.
Can someone please let me know how to speed this up and improve update performance out here. Is deletion and then addition of keys by design
Our application is multi-threaded and we are getting "Timeout error trying to lock table" issues for which I have added MVCC=true in the connection line and now ran into another problem.
Thanks