which is more scalable - view or raw table access+offload to application server

From: Timasmith <timasmith_at_hotmail.com>
Date: 6 Mar 2007 17:10:52 -0800
Message-ID: <1173229852.202565.319900@q40g2000cwq.googlegroups.com>

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

1. 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? Received on Tue Mar 06 2007 - 19:10:52 CST