Re: [Hsqldb-user] large tables - and archives

[Fred T. <ft...@cl...>]

You can adjust the cache_scale property to control the memory used by cached
tables. Reducing the scale by one will result in a 50% reduction.

Fred

----- Original Message -----
From: "Dmitri Colebatch" <di...@co...>
To: <hsq...@li...>
Sent: 17 February 2003 05:37
Subject: [Hsqldb-user] large tables - and archives


hey all,

Firstly, excuse me if this is covered somewhere, I haven't been able to find
anything... and the archives dont seem to be listed on the trusty
mail-archive.com.

I'm building a swing client for an ejb application, and have a whole bunch
of lookup data that I want to cache on the client (it rarely changes, if
ever).  I've set up a couple of tables so far, my .data file is almost 6mb,
and my .backup is just over a 1mb.  My .script file contains:

    CREATE CACHED TABLE S_POSTCODE(...)
    CREATE INDEX POSTCODE_IDX ON S_POSTCODE(PCODE)
    CREATE INDEX LOCALITY_IDX ON S_POSTCODE(LOCALITY)
    CREATE INDEX STATE_IDX ON S_POSTCODE(STATE)
    SET TABLE S_POSTCODE INDEX '2298936 2298936 5208 2664624 17121'
    CREATE CACHED TABLE S_CODE(...)
    CREATE UNIQUE INDEX CODE_UNIQUE_IDX ON S_CODE(CODE,CODETYPE)
    CREATE INDEX CODE_IDX ON S_CODE(CODE)
    SET TABLE S_CODE INDEX '5440472 5438216 5438216 7264'

I'm trying to have things as fast as possible... hence the indexes.  Now, as
I understand it a cached table will use up as much memory as is available
(?) to have fast and efficient queries.  However, the first query on the
s_postcode table takes about 14 seconds, and the exact same query
subsequently takes only 1 second.  I assume this is hsqldb building its
indexes in memory?  So I put a little pre-query in, so that by the time the
user needs the table, the indexes are already built.  Thing is, either way,
it takes about 14mb of memory to do this...

So, my question is - is 14mb more than people would expect to load the above
sized db, or is there a better way to do this?

cheers
dim



-------------------------------------------------------
This sf.net email is sponsored by:ThinkGeek
Welcome to geek heaven.
http://thinkgeek.com/sf
_______________________________________________
Hsqldb-user mailing list
Hsq...@li...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user