On 2018/07/20 3:38 PM, Philipp Cornelius wrote:
One problem I have repeatedly faced is the performance for large updates. I have several tables with >100,000 rows and I've used EXPLAIN ANALYZE UPDATE ... and that, too, takes ages (I understand EXPLAIN ANALYZE is run before the update statement anyway?).
EXPLAIN ANALYZE .... runs the command, and collects stats along the way so that it can show the stats together with the plan. Your best bet is to run it on a sample size large enough to show the problem without being so large it never completes.
than a full scan? It almost seems like the time needed to complete the UPDATE grows exponentially in the number of rows
Depending on the exact query, your table structure, the version you use, the H2 URL params you use, you may indeed be triggering some kind of O(n^2) behaviour.
You can also try profiling, using one of the available profiling tools, including the light weight one built into H2: http://h2database.com/html/performance.html#built_in_profiler -- 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 https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
