in reply to Re (tilly) 2: Performance quandary
in thread Performance quandary
it seems to me you are wanting an in-memory data structure, but you are tying the data to a database structure. if you are going to go this route, i would suggest looking into the index optimizations of BDB as tilly suggested with an 'undef' file, MySQL HEAP tables, and pure-perl structures like DBD::AnyData. A good index system will make or break your seek time, and you are seeking alot aren't you? i'm not sure how much overhead a connection to BDB or MySQL would add, but if it is significant DBD::AnyData in-memory table may be less by virtue of running as a perl module and not connecting to anything at all.
if you are doing concurrent reads/writes the locking is of importance to you as well. you probably want to avoid anything that locks more than just the relevent rwo. if you can guarentee integrity another way, or 100% accuracy is unnecessary, maybe you can avoid locking the table at all.
if you aren't doing concurrent processing, since you are farming the data-storage out to BDB you might be able to get away with (A) building the trees you have to make in a child process so several can be done concurrently, and (B) splitting your tables so you can do concurrent reads/writes. this probably won't get you much, but it would allow you to avoid some locking problems if your tables do lock, and it would pottentially allow you to run a separate DB and/or tree building on a second proceesor/machine to share the load.
you also want to avoid anything resembling transaction logging as that inevitably is designed to trade speed for data integrity. i don't know BDB well, but in MySQL you would want to avoid InnoDB tables just for this reason.