It is not about execution time, but data size. The dba's don't like it when I tell them I am pulling x times more data than before.
I am not against prefetching. In fact, I had it as a prefetch, but, they balked about the potential increase in data returned. On Tue, Apr 28, 2015 at 3:51 PM, Len Jaffe <lenja...@jaffesystems.com> wrote: > Prefetch generates SQL joins. The join is the FUNDAMENTAL unit of work in > a relational database. In 25 years, the only time I've ever seen a join not > be faster than decomposing into multiple queries is when the queries > involve millions of rows in one or more tables, and/or when the RDBMs query > optimizer ran into a pathological case, or a bug. > > Try an experiment. Benchmark the query using a column-specified prefetch, > and then benchmark running the first query, extracting the list of foreign > keys, and runnign a second query to return all of those. I'd like to see > the difference. > > I predict that for 100 records, the difference in execution time will be > below the threshold where it makes any sense to optimize the join away. > > Len. > > > > > On Tue, Apr 28, 2015 at 6:37 PM, Kevin Karabian <kkarab...@turnitin.com> > wrote: > >> Yes, but the problem is for a has_many, prefetch causes multiple rows to >> be returned. For example if a result object has 10 related objects then 10 >> rows are returned for that one object. If the result object is large, then >> that is a lot of repetition. I want to basically run a query that will >> return only the 10 related objects and then put those into the result >> object eliminating the repetition. >> >> And actually my use case is that I want to do this for a large number or >> result objects so I am amortizing the cost by grabbing all the related >> objects for all the result objects in question. So say, I have 10 objects >> and each has 10 related objects. I get the 100 related objects at once and >> populate the result objects with the relationship data. >> >> On Tue, Apr 28, 2015 at 3:27 PM, Dagfinn Ilmari Mannsåker < >> ilm...@ilmari.org> wrote: >> >>> Kevin Karabian <kkarab...@turnitin.com> writes: >>> >>> > Hi, >>> > >>> > Is there a way to store already retrieved related objects (related as a >>> > has_many) in a result object, such that calling the accessor for that >>> > relationship data will not hit the db again. >>> >>> This is exactly what prefetch is for. >>> >>> https://metacpan.org/pod/DBIx::Class::ResultSet#prefetch >>> https://metacpan.org/pod/DBIx::Class::ResultSet#PREFETCHING >>> >>> -- >>> - Twitter seems more influential [than blogs] in the 'gets reported in >>> the mainstream press' sense at least. - Matt McLeod >>> - That'd be because the content of a tweet is easier to condense down >>> to a mainstream media article. - Calle Dybedahl >>> >>> >>> _______________________________________________ >>> 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/dbix-class@lists.scsys.co.uk >>> >> >> >> >> -- >> >> >> >> >> *Kevin KarabianSenior EngineerTurnitin – www.turnitin.com >> <http://www.turnitin.com/>kkarab...@turnitin.com >> <j...@turnitin.com>510.764.7529 <510.764.7529>* >> >> _______________________________________________ >> 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/dbix-class@lists.scsys.co.uk >> > > > > -- > Len Jaffe - Information Technology Smoke Jumper - > lenja...@jaffesystems.com > 614-404-4214 @LenJaffe <https://www.twitter.com/lenJaffe> > www.lenjaffe.com > Host of Columbus Code Jam <http://www.meetup.com/techlifecolumbus/> - > @CodeJamCMH <https://www.twitter.com/CodeJamCMH> > Curator of Advent Planet <http://www.lenjaffe.com/AdventPlanet/> - An > Aggregation of Online Advent Calendars. > > > _______________________________________________ > 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/dbix-class@lists.scsys.co.uk > -- *Kevin KarabianSenior EngineerTurnitin – www.turnitin.com <http://www.turnitin.com/>kkarab...@turnitin.com <j...@turnitin.com>510.764.7529 <510.764.7529>*
_______________________________________________ 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/dbix-class@lists.scsys.co.uk
