Re: algorithm help for determining efficient db data retrieval

IMHO you are digging yourself a hole. An object where you want one chunk of properties in one circumstance and another set of properties in another smells like two or more objects to me. You sound like you are trying to optimise somthing that does not even work yet. Chances are the bottlenecks won't be where you suspect.

You don't seem to understand what takes time and what does not. When you retrieve data from a DB it takes almost exactly the same time to pull one byte as it does to pull lots of bytes. The reason is that a disk will deliver something like 50MB per second and the connect/parse/bind/execute/seek/disconnect overhead is the same (almost) no matter how much data you pull. The actual data delivery time is ~ 10% or less of the total transaction time.

If you ignore the simple fact that it is far easier/faster for a DB to pull a chunk of data (say a serialised object) than it is for it to pull a whole lot of bits from that 'object', your approach will be be slower simply because the best case is you shave a few % off a single transaction (it is arguable it will not take *longer* due to increased query complexity) but a second transaction at any stage will add 95% + which totally wipes out any gain.

I would suggest you rethink your approach. KISS and just pull all the data you need in one pass. If when you have it running you find a bottleneck then look at optimising it.

Storable and FreezeThaw are two good serilisation solutions. Typically you don't want to store serialised object in a DB as you loose most of the value of using a RDBMS, but if you have good reasons to do it these modules can help.

cheers

tachyon

Comment on Re: algorithm help for determining efficient db data retrieval

Replies are listed 'Best First'.
Re: Re: algorithm help for determining efficient db data retrieval by AidanLee (Chaplain) on Apr 06, 2004 at 02:35 UTC
Well, it's good to know that transferring a little vs a lot isn't a huge issue. That I did not know. I'm less concerned with my ability to pull some or all of a single object's properties than i am with the possibility of having to pull a small set of properties for say 400 objects, but then wanting a larger set of properties for 10 of them. While each object may be fairly lightweight (say a few hundred to a thousand Kbytes), i'm worried what happens when i've got to deal with objects en masse. I am aware of Storable and FreezeThaw, but at least for the time being a relational database is best for me because it allows for robust searching out of the box, and is a platform for modeling relationships between objects. That said though, i fully appreciate the warning about digging that hole	[reply]
Re: Re: Re: algorithm help for determining efficient db data retrieval by tachyon (Chancellor) on Apr 06, 2004 at 03:06 UTC
If by efficiency you mean speed the simple fact is that you spend memry to get more speed. Accessing a DB (on disk) is slow compared to accessing data in memory. You can spend memory in two ways. Give your DB huge ammounts of memory to cache disk pages and indices (so it does not have to hit the disks as often) or simply pull all the data you need into memory one pass. With RAM priced at a few hundred bucks a GB and programmer time to code a more memory efficient solution at a similar level per day there is a very good business case for throwing lots more memory at a problem to save code complexity and thus programmer time and debugging time and future maintenance issues. Perl loves lots of memory. So do RDBMS. If you are doing much work with either a small investment on more RAM has long term wide ranging benefits. cheers tachyon	[reply]


Your skill will accomplish what the force of many cannot
	PerlMonks