Re: please give examples of where using cdbi to produce comlex report queries is truly a bad idea

[prev] [thread] [next] [Date index for 2005/03/06]

.--- .- -- . ... wrote:
> hi list and specifically Perrin,

Hello .--- .- -- . ...,

> I've read more than one post by you suggesting that using cdbi to
> produce complex reports is a bad idea.

Tony has said similar things in some situations.  Note that I mean 
reports spanning multiple tables and usually using fancier functions of 
SQL (GROUP BY, HAVING, subqueries, etc.).  If your reports are so simple 
that you can do them with nothing but standard CDBI methods (no custom 
SQL), then none of this applies except the efficiency concerns.

> Can you please give me some examples of why you believe this is the
> case?  Besides the instances involving frequent database hits and
> memory usage of loading lots of big objects into memory (not to say
> that those are not severe enough concerns).

Besides the efficiencies you mention above, there is programming 
efficiency.  Duplicating the kind of report you can write with a 
well-crafted SQL query using CDBI might take a lot of coding, and it 
would probably involve duplicating things that your database is really 
good at (set operations, for example) in perl code.

Another part is conceptual.  I often see people trying to jam a large 
query that results in multiple types of objects into one of their CDBI 
classes.  That doesn't make sense: why does this one class own a query 
that spans multiple classes?  I use hand-written SQL with CDBI, but only 
when it results in a list of objects of one class.  Otherwise, it forces 
you to make one class know a lot about another, or do kludgy things with 
TEMP fields.  You encapsulation falls apart.

Quick example, using the CD database: Getting a list of the most popular 
CDs by joining against some playlist table could be done comfortably as 
a set_sql() in the CD class.  It results in a list of CDs, in a specific 
order, and that's all.

If you wanted to add the number of plays into that report, and the "last 
played" date, it would no longer make sense through CDBI, since it no 
longer maps to any CDBI object.  You would be jamming a query into the 
CD class that doesn't result in a list of CDs, and you'd end up doing 
awkward workarounds.  I would write a custom query for that in a 
separate class, and just generate a perl data structure from it.

I hope that makes it clearer.

- Perrin