[Dbix-class] Why is prefetch so slow?

[Jon Schutz]

In my code I have a DBIx::Class query with prefetch that is taking about
1.2 seconds to execute.  If I remove the prefetch, it takes 0.2 seconds.
The actual SQL query takes 0.00 seconds with or without the join.  The
number of records is the same with and without the prefetch/join.

I appreciate the DBIx::Class is building additional objects on prefetch,
but surely that can't have that great an overhead!

Using '+select' instead of prefetch adds much less extra time (but
screws up the otherwise clean object model in the code).

To demonstrate, I have pared down the code and put together a test case
(attached), and run it on various servers.  It is a very simple schema.
The code with prefetch takes about 4 times longer to run.  The
equivalent code using DBI has almost constant run time with/without the
join.

Sample results:

# Server A
Operation with prefetch took 0.146116 secs
Operation without prefetch took 0.035811 secs
Repeat operation with prefetch took 0.15038 secs
DBI operation with prefetch took 0.000979 secs
DBI operation without prefetch took 0.000793 secs
Repeat DBI operation with prefetch took 0.000762 secs

# Server B
Operation with prefetch took 0.085375 secs
Operation without prefetch took 0.0166269999999999 secs
Repeat operation with prefetch took 0.082367 secs
DBI operation with prefetch took 0.001724 secs
DBI operation without prefetch took 0.001696 secs
Repeat DBI operation with prefetch took 0.001644 secs

# Server C
Operation with prefetch took 0.073623 secs
Operation without prefetch took 0.014567 secs
Repeat operation with prefetch took 0.070953 secs
DBI operation with prefetch took 0.001508 secs
DBI operation without prefetch took 0.001418 secs
Repeat DBI operation with prefetch took 0.001377 secs

# Server D
Operation with prefetch took 0.39944 secs
Operation without prefetch took 0.114976 secs
Repeat operation with prefetch took 0.422341 secs
DBI operation with prefetch took 0.001064 secs
DBI operation without prefetch took 0.000984 secs
Repeat DBI operation with prefetch took 0.000919 secs

Reliably, the 'without prefetch' time is about 4 times the 'with
prefetch' times for DBIx::Class, but almost equal for DBI.

Servers are running perl 5.8.8, DBIx::Class 0.08007, Linux (CentOS 4 and 5), MySQL 4 and 5.

Timings of DBI vs DBIx::Class are not a concern; for obvious reasons we
expect a difference there.

Is anyone aware of this performance issue, or, even better, does anyone
know how to fix it???

-- 

Jon


-------------- next part --------------
A non-text attachment was scrubbed...
Name: prefetch.pl
Type: application/x-perl
Size: 5352 bytes
Desc: not available
Url : http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20071012/ba09a4a2/prefetch.bin