For the simplest, fastest 1:1 key, value store, use "CREATE MEMORY TABLE IF NOT EXISTS store(key VARCHAR PRIMARY KEY HASH, value VARCHAR);"
If you are using numeric keys, you should compare performance for hashed and standard primary keys to see which one is faster for you. More on this far, far below, if you want the nitty-gritty. Note that you do not need to specify an additional UNIQUE or NOT NULL constraint on the key, since the primary key constraint includes this. Similar, you do not need additional indexes on the key column, although you may want an index on ht In answer to your questions: a)Yes. You're perfectly safe, but recreating the index & rebuilding from the log after an unclean shutdown may be very slow for large tables. The "NOT PERSISTENT" table option is the unsafe one, where everything is in memory only. It's really, really fast though. b)AFAIK you cannot control how much heap a MEMORY table uses, except by limiting how many rows it has. Bear in mind that (unless you know a lot about VM guts), java objects use more memory than you'd expect, so always increase the max memory more than you expect it to need (see below). The MEMORY_FREE() and MEMORY_USED() SQL functions will be helpful in estimating how big your table can get. Using a very large cache_size (yes, that was right) should give similar results to using a memory table. With either memory tables or large cache sizes, remember to give your Java app or H2 server instance plenty of memory! Use the "- Xmx512M" (where 512 is how many MB or RAM to use at max) command line option, and make sure this is significantly larger than your cache size. Further instructions on the command-line switch: http://javahowto.blogspot.com/2006/06/6-common-errors-in-setting-java-heap.html Cheers, Bob McGee On Jun 23, 9:20 am, Lukas Zapletal <[email protected]> wrote: > Ok, to wrap up: > > For 1:1 key:value mapping I should use unique hashed constriant and > memory table to keep indexes in the memory. > > One two questions about MEMORY TABLE (http://www.h2database.com/html/ > grammar.html#createtable) > > a) Are data within the MEMORY TABLE safe? What if anything bad occurs? > If I understand correctly H2 should start up and recreate index > automaticaly, there should be no data loss. > > b) Where can I manage memory consumation of the MEMORY TABLE index? I > mean indicies will not be stored in the main cache (CACHE_SIZE > setting), right? If I use DB in the embedded mode I think I will not > like H2 to eat all the heap. :-( > > Thank you very much for the support and help. > > LZ > > On 21 čvn, 13:25, Thomas Mueller <[email protected]> wrote: > > > Hi, > > > > SET UNDO_LOG 0 - turns off undo log (I do not need rollbacks) > > > SET LOCK_MODE ? > > > SET LOG ? > > > I wouldn't disable the undo log, unless you have very large > > transactions. Also, I wouldn't change the other settings unless you > > fully understand what those mean - see the documentation for more > > information. > > > > Clustered indices are designed for that sort of thing, but AFAIK H2 > > > doesn't support that yet; maybe when the new page store scheme is > > > implemented? > > > Yes, that's the plan. Clustered indices should be easy to add once the > > "page store" is done. > > > > settings ... for optimizing queries on large tables > > > Sorry, I don't think there are settings that would help. > > > 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 -~----------~----~----~----~------~----~------~--~---
