Re: [CDBI] Make CDBI go fast

On Fri, Apr 20, 2007 at 03:59:37PM -0400, Perrin Harkins wrote:
> Take a look at the SQL that gets run when you hit a page in your CDBI
> app.  If there's a lot in there that you look at and think "why is it
> using 700 SQL calls for that?!" then you will probably do better with
> Rose.  You also might not have to do as much work with set_sql and
> friends.

Yes, that's one thing I do religiously is look at DBI trace output and
make sure I only see the queries I expect.  I also have code I used
under development that tells me when CDBI is going back to the
database for an extra column.  It's easy to get Essential setup but
then later add an extra column display in a template and not think to
do the DBI trace again.

I realize that CDBI doesn't have any real iterators, but for most of
my pages I'm showing 50 or so rows max at a time, so I also assume I
should be hitting the database once for that.

I'm less clear on dealing with many-to-many fetches.  Albums can have
many Artists, but typically just one.  So, it probably makes sense to
do the join and potentially get duplicate Album rows back then hit
the database an extra time for each Album to fetch the related
Artist(s).  But, if the many-to-many relationships are much
larger then could end up bringing in a lot of extra rows.  RDBO is
flexible that way.

I need to look at the docs to see how DBIC deals with many-to-many
joins where a query could return duplicate rows of one table.

> In short, making the SQL efficient is by far the most important thing.

Yes, and you probably don't want to trust the ORM to always get it
right, either.

Thanks for the comments!

-- 
Bill Moseley
[email protected]