representing an entire table efficiently

[ahoward]

i am wondering about a pattern that would most efficiently represent an entire table - my clients will be needing the entire (large) table in 90% of use cases... if in create an entity bean with references to all the rows (more entity beans) and specify eager load that gets me part of the way there, but the TableEJB itself would then need mapped to the db, and could easily be passivated... essentially, i want to 'pin' an entire table in memory, or at least *heavily* optimize retrival of all rows...

[hstech]

This is going to depend a lot on a number of considerations:

(1) How big is the table.
(2) How static are the contents of the table.
(3) How quickly do you need to get access to the data.
(4) Is concurrent access required? and how heavy a workload?
(5) Is there any special business logic that needs to be implemented?

For example, for a small table that is very static that does not require heavy access, you could populate a serializable Java Bean and store it in a JNDI registry.

Cheers,
Aaron.

[ahoward]

(1) How big is the table.

very, > 300,000 rows

(2) How static are the contents of the table.

changes are made on the order of one row
per day...

(3) How quickly do you need to get access to the data.

as many as 3000 lookups per/hour, for up to
several hundred hours consecutively

(4) Is concurrent access required? and how heavy a workload?

yes. for all threads, see above

(5) Is there any special business logic that needs to be implemented?

no. pure look ups - eg. i'll create thread safe hashtable or some such...

so, you see this is not the trivial case. i'm experimenting with javaspaces and have not yet found a better solution... any thoughts?

-ara

[drcharris]

If you're prepared to use a LOT of memory (and it sounds like you are), and your reason for not using the database directly is one of speed, have you thought about an in-memory database?

Hypersonic can do in-memory operation, although I've no idea whether it can handle 300000 rows. You could probably rig up something to load the data into hypersonic at server startup.