Hi Thomas, Thanks for your reply.
Here's some background info * I'm running the current SVN source from trunk * I'm using an Intel-based iMac running the latest, fully updated Mac OS X Snow Leopard (10.6.7) and latest Mac java 1.6.0_24 * The database is 2.9 Gigabytes on the hard disk * I run the full ANALYZE command without parameters as the first thing I do after establishing a database connection. I prepare the system with a simulated cold boot running the Mac OS X 'purge' command (so nothing cached in inactive memory) * It takes roughly 40 seconds * On subsequent program restarts - without cold booting - it takes about 2 seconds. I added a bit of time-logging to the source. Here are a list of all "PUBLIC" tables with the share of time they take to run ANALYZE: PUBLIC.CARD (52 rows, 2 columns) 1 ms PUBLIC.CASINO (5 rows, 2 columns) 1 ms PUBLIC.DAY (598 rows, 7 columns) 247 ms PUBLIC.GAME (588,939 rows, 4 columns) 807 ms PUBLIC.GAMEHANDHISTORYTEXT (588,939 rows, 2 columns) 3,036 ms PUBLIC.GAMETYPE (4 rows, 2 columns) 12 ms PUBLIC.HANDHISTORYFILE (8,040 rows, 3 columns) 10,540 ms PUBLIC.HANDTYPE (39 rows, 3 columns) 12 ms PUBLIC.PLAYER (85,490 rows, 5 columns) 1,040 ms PUBLIC.POKERTABLE (6,496 rows, 7 columns) 800 ms PUBLIC.POSITION (12 rows, 2 columns) 1 ms PUBLIC.PROPERTY (1 rows, 2 columns) 1 ms PUBLIC.RINGGAMEHEROHAND (494,770 rows, 65 columns) 2,284 ms PUBLIC.RINGGAMEHEROSUMMARY (5,226 rows, 45 columns) 2,088 ms PUBLIC.RINGGAMEPLAYERHAND (3,233,500 rows, 65 columns) 598 ms PUBLIC.RINGGAMEPLAYERSUMMARY (530,489 rows, 47 columns) 690 ms PUBLIC.RINGGAMEPLAYERTABLESIZESUMMARY (67,918 rows, 43 columns) 2,347 ms PUBLIC.STAKELEVEL (162 rows, 8 columns) 28 ms PUBLIC.STREET (5 rows, 2 columns) 1 ms PUBLIC.TOURNAMENT (1,560 rows, 14 columns) 576 ms PUBLIC.TOURNAMENTHEROHAND (94,156 rows, 67 columns) 1,917 ms PUBLIC.TOURNAMENTHEROSUMMARY (11,893 rows, 46 columns) 5,055 ms PUBLIC.TOURNAMENTPLAYERHAND (684,218 rows, 67 columns) 535 ms PUBLIC.TOURNAMENTPLAYERSUMMARY (174,416 rows, 49 columns) 842 ms PUBLIC.TOURNAMENTPLAYERTABLESIZESUMMARY (18,661 rows, 43 columns) 4,738 ms PUBLIC.TOURNAMENTSUMMARYTEXT (816 rows, 2 columns) 2,385 ms Total Time for ANALYZE: 40,582 ms I used jvisualvm to do some CPU profiling. Here's the top 20 methods by time taken while I ran ANALYZE after a simulated cold boot: Hot Spots - Method Self time [%] Self time Invocations org.h2.store.FileLock.run() 67.67956 154017.743 ms 1 org.h2.store.FileStore.readFully(byte[], int, int) 16.777565 38180.55 ms 26174 org.h2.store.WriterThread.run() 10.99363 25018.102 ms 1 org.h2.jdbcx.JdbcConnectionPool.getConnectionNow() 2.3047786 5244.963 ms 1 org.h2.expression.AggregateData.add(org.h2.engine.Database, boolean, org.h2.value.Value) 0.12634493 287.522 ms 5363991 org.h2.util.StringUtils.utf8Decode(byte[]) 0.116022356 264.031 ms 34498 org.h2.command.dml.Select.queryGroup(int, org.h2.result.LocalResult) 0.10003644 227.652 ms 26 org.h2.expression.Aggregate.updateAggregate(org.h2.engine.Session) 0.09850636 224.17 ms 5363991 org.h2.util.DateTimeUtils.getTimeGMT(long) 0.095480904 217.285 ms 124838 org.h2.store.Data.readValue() 0.08816752 200.642 ms 5398608 org.h2.index.PageDataLeaf.readRow(org.h2.store.Data, int, int) 0.08457037 192.456 ms 149389 org.h2.value.Value.cache(org.h2.value.Value) 0.07615842 173.313 ms 485264 org.h2.util.IOUtils.copyAndCloseInput(java.io.Reader, java.io.Writer, long) 0.072932586 165.972 ms 1460 org.h2.result.Row.getMemory() 0.06597909 150.148 ms 149077 org.h2.command.ddl.Analyze $1.actionPerformed(java.awt.event.ActionEvent) 0.05074108 115.471 ms 1 org.h2.store.PageStore.createData() 0.050208494 114.259 ms 26593 org.h2.store.FileStore.seek(long) 0.045320306 103.135 ms 26229 org.h2.store.PageStore.getPage(int) 0.040858805 92.982 ms 66042 org.h2.index.PageDataLeaf.getRowAt(int) 0.03753454 85.417 ms 150475 I hope this is all helpful info. I realise this is not something worth you spending too much time on. I've also hit on a work-around - I can write some code in my app that does what the ANALYZE command does, and just add progress monitoring to my own code. Regards, Steve On Jun 6, 8:20 pm, Thomas Mueller <[email protected]> wrote: > Hi, > > Well, the plan was that ANALYZE is quick by default, so no progress > bar is required. But if a progress bar is required, I think it would > also be required for other queries (because ANALYZE internally is a > list of queries). It could be done I guess, but it wouldn't very > accurate. > > Could you try to find out why ANALYZE is slow in your case? Is there > one table that uses most of the time, or do all the table use about > the same time? Do you just run "ANALYZE" (without parameters)? > > > Is this a patch you'd consider adding to the trunk? > > Let's first analyze what the problem is. If there is no other way to > solve it, then I guess it makes sense to try. > > Regards, > Thomas -- 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.
