Just to update that I have tried this on versions 1.3.161 and 1.3.169 and the there's no change in time taken.
On Sunday, 4 November 2012 19:28:23 UTC+5:30, Manik Dhawan wrote: > > 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 > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/yd_RA-spiNcJ. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
