Hi, I forgot about hash indexes. Those are only supported for in-memory databases, and may improve performance for insert, update, delete, and select. See:
http://www.h2database.com/html/grammar.html#create_index CREATE UNIQUE HASH INDEX ... http://www.h2database.com/html/grammar.html#create_table create table test(id int primary key hash, name varchar) I will add this to the documentation: " == In-Memory (Hash) Indexes == Using in-memory indexes, specially in-memory hash indexes, can speed up queries and data manipulation. In-memory indexes are automatically used for in-memory databases, but can also be created for persistent databases using CREATE MEMORY TABLE. In many cases, the rows itself will also be kept in-memory. Please note this may cause memory problems for large tables. In-memory hash indexes are backed by a hash table and are usually faster than regular (tree based) indexes. However, hash indexes only support direct lookups (WHERE ID = ?) and not range scans (WHERE ID > ? and so on). To use hash indexes, use HASH as in: CREATE UNIQUE HASH INDEX and CREATE TABLE ...(ID INT PRIMARY KEY HASH,...). " Regards, Thomas On Sat, Aug 8, 2009 at 8:17 AM, Bob McGee<[email protected]> wrote: > > > Any advice for tuning a database "in-memory mode" is welcome. > These tips are not in the documentation yet, but will be at some > point. > > For performance and compact storage, prefer the Double and Real types > rather than Decimal when not dealing with currency. > > Watch your memory use -- it is entirely too easy to get an > OutOfMemoryError when working with the memory modes if the table gets > too big. There are posts in the groups on various ways to avoid > this. In general, you should limit the number of rows and size of per- > row data. To prevent memory problems, Varchar/char, binary, and LOB > types should have hard limits on precision. > > My previous post on estimating memory use is not fully accurate -- I > have better information from source diving now, but hesitate to post > since the page store will change this. > > Regards, > Bob McGee > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
