We have a table with 50 columns – each of these columns would have another twin column.The table therefore would have 100 columns (table is 100 column wide).
For example if we have a column “A”, we have its twin column as “A_ID”. Column “A” would have a string value. Column “A_ID” would have a numeric_ID for that string value. Like this, we have 50 regular columns and 50 ID columns for them. We have about 100,000 rows in this table. All the column “A”s have their own individual index. We know which value of column “A” must get what ID value. Our SQLs are like follows. Update table XYZ set A_ID = ? where A = ? If unique values in Column “A” are N, then the above query would run N times for column A (updating ID value for each unique value of column “A”). Our problem – Each update statement is taking 2-3 minutes to complete. With this, it takes about 100-150 minutes for 50 columns (to update their 50 twin _ID columns). We noticed that if we reduce the width of the table (having less columns and therefore less ID columns), the update statements are taking little less time. We did add “ANALYZE” and we saw that index is being used in EXPLAIN. We tried setting up this table in MEMORY (and creating HASH indexes for it) but that is only giving us 10 second drop (10 seconds less than 2 minutes or 3 minutes). Other info = we are running H2 with these parameters - MAX_OPERATION_MEMORY=1610612736;CACHE_SIZE=268435456;TRACE_LEVEL_FILE=0;LOG=0;UNDO_LOG=0 We would like to bring down the update time to < 10 seconds for each column. This way, the overall time to update 50 columns would be < 500 seconds. Wondering how we can achieve that. Please suggest us some ideas. Appreciate your thoughts. Thanks, Pani -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
