[Dbix-class] Why is prefetch so slow?

[Will Hawes]

On 12/10/2007, Jon Schutz <jon+dbix at youramigo.com> wrote:
>
> 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


Have you verified what SQL statement is being generated by setting
DBIC_TRACE=3D1 prior to running your script? It's not clear from your
question. If not this might give more insight into the problem.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20071012/77d=
d1a42/attachment.htm