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

From: Jeremy <jeremy0505_at_gmail.com>
Date: Mon, 3 Jul 2006 09:03:27 +0100
Message-ID: <MPG.1f12df6029723498a232@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

Received on Mon Jul 03 2006 - 03:03:27 CDT