[sf-perl] ORM vs. DBI

[Bill Moseley]

On Tue, Mar 1, 2011 at 1:49 PM, Paul Makepeace <
Paul.Makepeace at realprogrammers.com> wrote:

> On Tue, Mar 1, 2011 at 17:26, Kevin Frost <biztos at mac.com> wrote:
> > For my money, I don't think I would do any serious development without at
> > least enough ORM to permanetly avoid hashrefs and arrayrefs in the app
> code.
>
> This on its own is huge. Being able to point the ORM at the database
> and have it auto-generate classes that'll give you accessors is really
> a big timesaver.
>

But, that's a one-time operation when converting from an existing database.
 Adding new classes (for new tables) is something I do by hand -- and it's
not that much work.



> DBIC covers some pretty complex SQL generation at this point. Sure, if
> you're doing heavy OLAP/data mining work then you'll be hand-crafting
> but for the majority (>99%?) of common use cases in development it'll
> have you covered. Most ORMs have had decent prefetching for years so
> that objection (lookups on related/joined/FK'ed) tables went away a
> long time ago.
>

DBIC (SQL::Abstract) makes it pretty easy to build almost any query.   It's
been very rare that I've had to fall back to writing SQL with DBIC.  And
result set chaining in DBIC means you can do some very useful things in code
where you might otherwise have to write a bunch of separate queries and
sprintf statements to dynamically build a query.   Promotes code reuse quite
a bit. Of course, connection management, nested transactions, and other
goodies are sure handy.  There's no doubt that DBIC is a big win over DBI
directly.

That said, it's easy to hang yourself with the rope DBIC gives you.  There's
some overhead with the ORM, of course.  But, it's really easy for developers
to write code that does crazy stuff in the database if not careful.  A few
nested loops in a template and you wonder why a request is taking so long.

You have to monitor the SQL that DBIC generates (or that you make DBIC
generate, really).  I recently added a distinct => 1 to a query that did a
prefetch and the query went from 18ms to close to a second.  Looking at the
SQL generated it was obvious what was happening.

You also have to monitor the number of queries generated by an operation (or
web request).  As I noted, careless use of DBIC in a few nested loops and
you end up doing hundreds or thousands of queries.   I have code that spits
out a warning under development when it looks like a request is making a lot
of queries or when spending too much time waiting on the database.  I'm
tempted to make it fatal.


-- 
Bill Moseley
moseley at hank.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.pm.org/pipermail/sanfrancisco-pm/attachments/20110301/9c06ff3d/attachment.html>