> The dba's don't like it when I tell them I am pulling x times more data than before. DBAs: This is why we can't have nice things.
So then just do two queries, perform in memory look-ups from one list of records into the other. On Tue, Apr 28, 2015 at 7:16 PM, Kevin Karabian <kkarab...@turnitin.com> wrote: > 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 > -- 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