Panayiotis Vlissidis, Thanks a lot for the reply. I made the changes as you suggested. Created multi-column indexes.
The time taken to update the table is 1.5 sec instead of 2 sec with MULTI_THREADED=1. Thanks, Suresh On Feb 14, 6:36 pm, suresh <[email protected]> wrote: > Hi, > > I am using in-memory h2 db and I have 8 tables. Each table has > 15 to 20 columns. One table with 60 columns. I have two indexes per > table. Each table will have at most 3k rows. > > Every 5 sec I am updating these tables with new data. I am > updating/inserting/deleting based on the delta. > It is real-time data feed. At the same time there will be 100 read > requests on these tables. > > I am not using any transactions. Plain update/select queries. > > I have tried with following two options : > > 1) MVCC=TRUE > > The time took to update a table is 4 sec. > The read queries takes 100 ms for each query > > 2) MULTI_THREADED=1 > The time took to update the a table is 2 sec. > The read queries takes 100 ms for each query > > I am using h2-1.2.147 version of h2. I would like to know what is the > best way to update the rows in the table less than 1 sec. > Each update contains less than 500 rows per table. > > I am finding interesting thing in TRACE options. > > /*SQL #:1 t:200*/UPDATE CURRENT_VALUES SET ATT1 = 9, ATT2 = 141, ATT3= > '141', \n\t\tATT4 = 3745, ATT5 = '3745', ATT5 = 232, ATT6 = '232', > ATT7 = 0, \n\t\tATT8 = '', ATT9 = 'xyz', ATT10 = 1297689071078, AT11 = > 0, ATT12 = 0,\n\t\tATT13 = 101 \n\t\tWHERE ATT2 = 141 AND ATT3 = 3745 > AND ATT4 = 232 AND ATT5 = 0 AND ATT9 = 'xyz'; > > 02-14 13:11:47 command: slow query: 1,091 ms > > The above query took more than 1 sec per row update. > > There is select query it took 1,429 ms, the table has 60 columns. > > Is there any way to optimize the performance? > > Thanks, > Suresh -- You received this message because you are subscribed to the Google Groups "H2 Database" group. 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.
