[coding] Mid-level data storage.
 

Looking for some advice from the olders-and-wisers in the crowd. I'm looking at a couple of situations where I've got 2 tables of data that needs to be referred to on a very regular basis that are around 100 rows. Because of their size and the frequency of their use I'm loathe to put them in the DB and create a huge number of calls - the way I see it I've got two other options that allow me to keep them as objects but on application servers as opposed to DB systems: XML file or a hard-coded array. It's feeling like a waste of RAM to keep them as arrays but a waste of CPU to keep them as XML files on the app servers. Thoughts from the crowd?

Well, you're between Scylla and Charbydis there. But if they're very volitile and/or frequently accessed, don't worry about the memory footprint vs. a DBMS because if they truly are hit hard, a good DBMS would hold them in memory buffers anyway. I'd say go ahead and make them Objects and hold them in a Collection with efficient access for your major use cases for a performance win over the longer call path for generalized SQL access. Serialize them in and out when you have to for persistance...XMLEncoder may be a good choice here.

Oh...this assumes you're writing Java or similar. Otherwise I extend my sympathies.

Ruby, it's not so bad. You have an excellent point on the buffering however.

Based on what I know about Ruby, I'm willing to classify it as "similar".

How many fields are there and what are the data types? How much memory is tied up in the array? If the data types are small and the overall memory footprint is small, I'd definitely go with an array.

Is the data grid completely filled or are there a lot of blanks? If there quite a few empty cells, you might think about multiple arrays or, for some things, creating an object with the values assigned as properties.

It pretty much depends on exactly what's in it (the data itself).

Depends on the access patterns. Something somewhat more elaborate than an array is likely called for. Fortunately Ruby has them.
I'm pretty sure Jag knows about sparse arrays.

Thank-you for the credit maggie. Around 100 rows, 4-6 fields, mostly integers but one or two short (<20 chars) strings. Completely filled. This stuff is almost entirely involatile but they will be hit very frequently, I should've mentioned all that in the first post, my bad.

Jag,

Here's what I did in ASP/IIS to eliminate this issue.

We did this for Lookup Tables in Oracle to cut down on query time (referencing lookup tables in Oracle queries causes your query time to expand greatly if you don't have the right architect designing your system).

What we would do is on the Application_OnStart event (which is when the App Server loaded), we'd load the lookup tables into global session variables as arrays, and then if they were changed, reload them. If you're looking to sync this across multiple app servers, you may want to look into having periodic updates :).

What we did after this was instead of referencing the database every time we had queries for the lookup tables, we'd reference the global session variables instead using functions. In other words, that extra 64K of RAM bought us a performance increase by a factor of at least 10, as we only had to hit RAM with compiled code to get the values to build the lookup table, as opposed to the overhead caused by hitting the DB.

Most app servers provide the ability to store global session variables, no?

I hope this helps,

Mitch

mitch, when this gets off the ground you're getting one big note of thanks somewhere, once again thanks for saving me a lot of work.

When I assumed you knew sparse matricies, I assumed you knew about caching too. :-)

I did, on both. However saying caching is all well and good but a neat, specific implementation guide rooted in production code is far more useful and one of a number of extremely nice and detailed replies that he's given me over time.

in the old days, we would have, on startup, created them as tables in a ram virtual drive.

I've always wondered: what did you feed the hampster?

wasn't so much as feedimg the hampster as keeping a roll of gaffa tape handy....

we ddin't get out a lot in them days.....

No story qualifies for the timeframe "the old days" if it involves a RAMdisk.

"The old days" requires at least 1/2" 8 track 6250bpi mag tape on open reels... but 80-column Hollerith punched cards are preferred.