which is more scalable for the database?

From: "Timasmith" <timasmith(at)hotmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: which is more scalable for the database?
Date: 2007-03-07 11:46:53
Message-ID: 1173268012.906695.70300@64g2000cwx.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Devrim GÜNDÜZ 2007-03-07 11:50:40 Re: real multi-master replication?
Previous Message Markus Schiltknecht 2007-03-07 11:14:04 Re: real multi-master replication?