3 Replies Latest reply on Dec 6, 2001 12:31 PM by Chris Harris

    representing an entire table efficiently

    ara howard Newbie

      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...

        • 1. Re: representing an entire table efficiently
          Aaron Smith Newbie

          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.

          • 2. Re: representing an entire table efficiently
            ara howard Newbie

            (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

            • 3. Re: representing an entire table efficiently
              Chris Harris Newbie

              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.