[Nitro] Speed up SELECTS (using PSQL VIEWS)

Kashia Buch kashia at vfemail.net
Sun Jan 15 18:11:05 EST 2006


it seemed to me nitro was a bit slow for heavily normalized databases, but I found a way around it using PostgreSQL VIEWs.
Maybe someone else can tell me, if there's such a function already in Nitro I just didn't discover? would be nice.

So, on to my example.

Suppose you have a model like

class File < Nitro::Controller
   is Taggable
   property :title, String
   has_one :filetype, FileType

   # ... blabla

using it in a xhtml like:

<?r for i in rows ?>
  #{i.title} : #{i.tags.join(", ")}
<?r end ?>

will query the database for each tag, which makes Og encredibly inefficient.
Now, if you do this:

* create an additional View class
class FileView < Nitro::Controller
   #is ViewOnly # << something like this would be cool though

   property :title, String
   property :tags, String
   property :filetype, String

   # this should be done automaticly/more efficient by Glue::ViewOnly
   def add_tag=; raise "write disabled"; end
   def filetype=; raise "write disabled"; end
   def filetype_oid=; raise "write disabled"; end

* create a VIEW in psql
CREATE VIEW ogviewfile AS
     SELECT c.oid,
       array_to_string(ARRAY(SELECT ogtag.name FROM ogtag,ogj_file_tag WHERE ogj_file_tag.container_oid=c.oid AND ogj_file_tag.tag_oid=ogtag.oid), ', ') AS tags,
     FROM ogfile c
       LEFT OUTER JOIN ogfiletype f ON (f.oid = c.filetype_oid);

obviously create the view, before you execute your nitro/og project, since og would create a new ogviewfile table, which we don't want.
(Use normal, INNER joins, to get still better speed..., the ARRAY(subquery) makes an array of the given column, array_to_string is basically the same as array.join(", "))
This VIEW basically presents us all the information we want, a neat sql table like:

oid | title | tags | filetype
1   |  asdf | a, b |

which is used by og.

the xhtml can now look like:

<?r for i in rows ?>
  #{i.title} : #{i.tags}
<?r end ?>

and only a single SQL query will be made for the whole page (which could be thousands of rows, depending on what you want to do), which leads to an unbelievable speedup in your Og/Nitro application.

This might be possible with other RDBMS, but I don't know.

So, hope I might've helped someone get some ideas, and, I want a Glue::ViewOnly module, so get coding ;)
*sigh* ah well, with my luck something like this is already in Nitro, and I just overlooked it, I love doing useless work.... ..

Good night everyone

Feel the love

More information about the Nitro-general mailing list