[CDBI] Make CDBI go fast

John Siracusa siracusa at gmail.com
Fri Apr 20 21:30:51 BST 2007


On 4/20/07, Perrin Harkins <perrin at elem.com> wrote:
> On 4/20/07, Bill Moseley <moseley at hank.org> wrote:
>> The database is often cited as the slow point in a web application, so
>> that adds weight to these benchmarks.
>
> Or the other way around, actually.  It's the database that's the bottleneck,
> not the ORM.  Two ORMs that generate the same SQL are going to be pretty
> similar in most real applications.
>
> The thing is, they don't generate the same SQL.  Class::DBI notoriously does
> things inefficiently with regard to SQL (joins, bulk deletes, more fetches
> than needed sometimes, etc.).  You're working around some of this by using
> set_sql a lot.  You might not have to with Rose.

Incidentally, the SQL generated by each ORM in the RDBO benchmark
suite is usually very similar in the very simple tests (e.g., insert,
update, delete).  One factor nearly as important as the generated SQL
in the simple tests is how DBI is used.  A module doing prepare(),
execute(), and fetchrow_hashref() is going to be much slower than a
module doing prepare_cached(), execute(), bind_columns(), and fetch().

That said, the biggest gap in performance is between modules that know
how to do joins and those that don't.  After that, the there's some
more variance at the top based on support for the various "special"
SQL variants like using subselects for limiting ...-to-many JOIN
queries, supporting MySQL's ON DUPLICATE KEY UPDATE syntax, etc.

-John



More information about the ClassDBI mailing list