Re: PL/SQL / web app / design question (text dictionary)

"Jeremy" <jeremy0505_at_gmail.com> wrote in message news:MPG.1f12df6029723498a232_at_news.individual.net...
: ============================================================
: ENVIRONMENT:
: Oracle 9iR2 / Oracle HTTP Server / mod_plsql / Solaris 8
: ============================================================
:
:
: Application I am working on will have be language independent on the
: pages used by administrators. To achieve this will mean that all text is
: stored in the database and a site or user profile setting will determine
: what language to use.
:
: I am considering the efficiency aspects at the moment. It is a web
: application using the PL/SQL web toolkit (mod_plsql).
:
: In mind I have a table structure something like this:
:
: create table xx_system_texts
: (text_type varchar2(10),
: language varchar2(30) default 'DEFAULT',
: item varchar2(80),
: text varchar2(80))
: tablespace tbs1;
:
: text_type = 'PROMPT','MESSAGE'
: item = a code to access the translated text
:
:
: e.g.
:
: text_type language item text
: --------- -------- ----- ----
: PROMPT FR FIRST_NAME Prenom
: PROMPT FR CITY Ville
:
: Then we will have a utility function so that we can display the
: appropriate words on the page:
:
: e.g.
: htp.p(util.gettext(p_text_type => 'TEXT',
: p_language => 'FR',
: p_item => 'FIRST_NAME',
: p_default_text => 'First Name');
:
: If you're with me so far that's good.
:
: The question is coming now. As I see it there are two ways of writing
: the function util.gettext - one is to simply locate the record in
: xx_system_texts and return the text value it locates. The other is to,
: at the start of each main procedure, read all the rows from the
: xx_system_texts table and populate an array - and then the function
: util.gettext will locate the matching row from the array :
:
: e.g.
:
: for i in 1..g_text_item.count
: loop
: if upper(g_text_item(i)) = upper(p_item) and
: upper(g_text_type(i)) = upper(p_text_type) then
: return g_text_text(i);
: end if;
: end loop;
: return p_item;
:
:
: My question is really about the efficiency of the two approaches. Let's
: say there may be typically 100 items of text that need to be translated
: on an average screen. This means that every page that is displayed will
: incur 100 direct reads of the xx_system_texts table. Assuming we have
: the indexes set-up so that these are as efficient as possible, do you
: think it is more efficient to read ALL text rows into an arrray at the
: outset and then red from the array?
:
: I know I could do some testing of the relative merits (will do shortly)
: but wanted to get a steer from anyone who might have faced this dilemma
: before. I anticipate perhaps 2,000 text items max.
:
: Extra info: a fresh session is created for every interaction with the
: server, so every page that loads will incur the same overhead. Is there
: a way to tell Oracle to "pin" a table in memory for all sessions with a
: periodic refresh?
:
: Open to alternative suggestions too ;)
:
:
: --
: jeremy
:

some quick thoughts / things that spring to mind

the database can cache small tables
frequently accessed blocks tend to be cached anyway IOTs or clusted tables may help reduce the amount of data being processed associative arrays in PL/SQL would allow quicker access preloaded data

++ mcs Received on Mon Jul 03 2006 - 07:20:06 CDT