On 12/10/2007, Jon Schutz <[EMAIL PROTECTED]> 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=1 prior to running your script? It's not clear from your question. If not this might give more insight into the problem.
_______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[EMAIL PROTECTED]
