From: Ryan Pavlik <rpav@...>
Date: 2004-04-21T07:25:29+09:00
Subject: Re: The quest for opensource database...

On Wed, 21 Apr 2004 06:38:06 +0900
Shu-yu Guo <shu@rufuran.org> wrote:

> I love PostgreSQL as well, and my main gripe with it is speed. I
> often run into situations where classes correspond nicely to tables
> (like a User class to a users table), so I'm tempted to write code
> which would automatically query the database for the neccessary
> information in its initialize. However, this gets quite slow because
> it becomes hard to combine simple queries into complex ones (which
> execute much much faster). I have made optimizations around this,
> but they're not as nice to use as just pure data encapsulation.
<snip>

There are a couple problems here, and a few solutions to them, as
well.  I'll shamelessly plug Criteria here.  Make sure you use the SVN
version:

  http://svn.mephle.org/svn/mephle/criteria/

That will help you combine your queries; I do this regularly:

  class Customer
      # This is the table, you probably want to use a DBITable though.
      IDX = SQLTable.new("my_table", ...)
  
      CUSTOMER_NAME   = (IDX.name == IDX.x0?)   # .x0? is a placeholder
      CUSTOMER_BEFORE = (IDX.date <  IDX.x0?)
      # :
      # :     more queries
      # :
  end
  
Later on we can combine them:

  query = CUSTOMER_NAME["Bob"] & CUSTOMER_BEFORE[1975]

You get the idea.  However this is only one of your problems; the
other is the fact it's very slow to do a query everytime you want an
attribute, either getting or setting.  For this I would recommend
"caching" things; make an attr_sql_accessor or the like which (perhaps
using Criteria... I do this too) makes note of when the value changes,
but only updates the database on a flush, and/or tie in transactions
(especially nice with PgSQL's MVCC).  This should keep you from having
3 lines like this:

  customer.name    = "Larry"
  customer.date    = 1998
  customer.address = "..."

...generate 3 UPDATE statements, but only one instead.  The only thing
you need to watch out for is making sure someone doesn't update the
database behind you; for this I'm not sure what you can do.  (Maybe
some magic with stored ruby procedures and triggers... dunno.)

-- 
Ryan Pavlik <rpav@mephle.com>

"Feh. At least we can agree that if anything goes wrong,
 it was due to *your* acute negativism" - 8BT