Re: which is more scalable for the database?

Timasmith wrote:
> Suppose I have a database table with 20 fields which are lookups to a
> single table.
>
> configtable(configtable_id, a_field, something_lookup_id,
> another_lookup_id, ...)
> lookup(lookup_id, value, description, ...)
>
>
> what is going to be faster to map the rows to an Object which needs
> the 'value' for every field ending in lookup_id
>

How long is ball of string 'a' compared to ball of string 'b'?

That will depend on a lot on how large the lookup table will be (100
rows or 10 million rows?) and on how long your descriptions are. Will
you want all 20 descriptions for each query you run? If your
descriptions are 200 characters each then maybe you can reduce some data
transfer by caching these at the client instead of transferring them
each time you retrieve a result. But then how will you keep your local
cache up to date?

I would suggest the view for a couple of reasons - first it will
simplify your queries as you won't need all the join details in every
query you run. Second the select for the query can be parsed and stored
whereas separate selects will be parsed each time.

A lot of this will depend on what you are doing and what you are using
to do it. If you are using php then you have less options for caching
than say a client program written in c. php may run pretty fast but it
won't outrun a compiled c program.

> a) View
>
>
> select c.configtable_id, l1.value as something_lookup, l2.value as
> another_lookup
> from configtable c,
> lookup l1,
> lookup l2
> where c.something_lookup_id = l1.lookup_id
> and c.another_lookup_id = l2.lookup_id
>
>
> foreach row
> map values to object
> end
>
>
> b) Cache all lookup values and populate
>
>
> select c.* from configtable
>
>
> foreach row
> map values to object
> if lookup_id
> find value from hashtable and map value to object
> endif
> end
>
>
> It seems that the latter *might* be better to scale outward better,
> as
> you could add application servers to do the caching/mapping and you
> only select from a single table?
>

Maybe but then postgresql may do the lookups quicker than what you have
available at the client end.

The only way you will really know is to load up some sample data and
test each method yourself.

How far are you expecting to scale? The simplest method may just work
fast enough that all the extra caching and setup/maintenance of this
will outweigh any benefit.

--

Shane Ambler
pgSQL(at)Sheeky(dot)Biz

Get Sheeky @ http://Sheeky.Biz