<div dir="ltr"><div>> <span style="font-size:12.8000001907349px">The dba's don't like it when I tell them I am pulling x times more data than before.</span></div><div><span style="font-size:12.8000001907349px">DBAs: This is why we can't have nice things.</span></div><div><br></div>So then just do two queries, perform in memory look-ups from one list of records into the other.<div><br></div><div><br></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Tue, Apr 28, 2015 at 7:16 PM, Kevin Karabian <span dir="ltr"><<a href="mailto:kkarabian@turnitin.com" target="_blank">kkarabian@turnitin.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">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. <div><br></div><div>I am not against prefetching. In fact, I had it as a prefetch, but, they balked about the potential increase in data returned.<div><div class="h5"><br><div class="gmail_extra"><br><div class="gmail_quote">On Tue, Apr 28, 2015 at 3:51 PM, Len Jaffe <span dir="ltr"><<a href="mailto:lenjaffe@jaffesystems.com" target="_blank">lenjaffe@jaffesystems.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div>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.</div><div><br></div>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.<div><br></div><div>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.</div><div><br></div><div>Len.<br><div><br></div><div><br></div><div><br><div class="gmail_extra"><div><div><br><div class="gmail_quote">On Tue, Apr 28, 2015 at 6:37 PM, Kevin Karabian <span dir="ltr"><<a href="mailto:kkarabian@turnitin.com" target="_blank">kkarabian@turnitin.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">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. <div><br></div><div>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.</div></div><div class="gmail_extra"><div><div><br><div class="gmail_quote">On Tue, Apr 28, 2015 at 3:27 PM, Dagfinn Ilmari Mannsåker <span dir="ltr"><<a href="mailto:ilmari@ilmari.org" target="_blank">ilmari@ilmari.org</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span>Kevin Karabian <<a href="mailto:kkarabian@turnitin.com" target="_blank">kkarabian@turnitin.com</a>> writes:<br>
<br>
> Hi,<br>
><br>
> Is there a way to store already retrieved related objects (related as a<br>
> has_many) in a result object, such that calling the accessor for that<br>
> relationship data will not hit the db again.<br>
<br>
</span>This is exactly what prefetch is for.<br>
<br>
<a href="https://metacpan.org/pod/DBIx::Class::ResultSet#prefetch" target="_blank">https://metacpan.org/pod/DBIx::Class::ResultSet#prefetch</a><br>
<a href="https://metacpan.org/pod/DBIx::Class::ResultSet#PREFETCHING" target="_blank">https://metacpan.org/pod/DBIx::Class::ResultSet#PREFETCHING</a><br>
<span><font color="#888888"><br>
--<br>
- Twitter seems more influential [than blogs] in the 'gets reported in<br>
the mainstream press' sense at least. - Matt McLeod<br>
- That'd be because the content of a tweet is easier to condense down<br>
to a mainstream media article. - Calle Dybedahl<br>
<br>
<br>
_______________________________________________<br>
List: <a href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class" target="_blank">http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class</a><br>
IRC: <a href="http://irc.perl.org#dbix-class" target="_blank">irc.perl.org#dbix-class</a><br>
SVN: <a href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/" target="_blank">http://dev.catalyst.perl.org/repos/bast/DBIx-Class/</a><br>
Searchable Archive: <a href="http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk" target="_blank">http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk</a><br>
</font></span></blockquote></div><br><br clear="all"><div><br></div></div></div><span><font color="#888888">-- <br><div><b style="font-family:Times"><font size="1"><span style="font-family:Arial;font-weight:normal;vertical-align:baseline;white-space:pre-wrap">Kevin Karabian</span><span style="font-family:Arial;font-weight:normal;vertical-align:baseline;white-space:pre-wrap"></span><br><span style="font-family:Arial;font-weight:normal;vertical-align:baseline;white-space:pre-wrap">Senior Engineer</span><br><span style="font-family:Arial;font-weight:normal;vertical-align:baseline;white-space:pre-wrap">Turnitin – </span><a href="http://www.turnitin.com/" target="_blank"><span style="font-family:Arial;color:rgb(0,0,255);font-weight:normal;vertical-align:baseline;white-space:pre-wrap">www.turnitin.com</span></a><span style="font-family:Arial;font-weight:normal;vertical-align:baseline;white-space:pre-wrap"></span><br><span style="font-family:Arial;color:rgb(0,0,255);font-weight:normal;vertical-align:baseline;white-space:pre-wrap">kkarabian<a href="mailto:jdoe@turnitin.com" target="_blank">@turnitin.com</a></span><span style="font-family:Arial;font-weight:normal;vertical-align:baseline;white-space:pre-wrap"></span><br><span style="font-family:Arial;font-weight:normal;vertical-align:baseline;white-space:pre-wrap"><a href="tel:510.764.7529" value="+15107647529" target="_blank">510.764.7529</a></span></font></b></div>
</font></span></div>
<br>_______________________________________________<br>
List: <a href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class" target="_blank">http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class</a><br>
IRC: <a href="http://irc.perl.org#dbix-class" target="_blank">irc.perl.org#dbix-class</a><br>
SVN: <a href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/" target="_blank">http://dev.catalyst.perl.org/repos/bast/DBIx-Class/</a><br>
Searchable Archive: <a href="http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk" target="_blank">http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk</a><br></blockquote></div><br><br clear="all"><div><br></div>-- <br></div></div><div><div dir="ltr"><div><div dir="ltr"><div>Len Jaffe - Information Technology Smoke Jumper - <a href="mailto:lenjaffe@jaffesystems.com" target="_blank">lenjaffe@jaffesystems.com</a> </div><div><a href="tel:614-404-4214" value="+16144044214" target="_blank">614-404-4214</a> <a href="https://www.twitter.com/lenJaffe" target="_blank">@LenJaffe</a> <a href="http://www.lenjaffe.com/" target="_blank">www.lenjaffe.com</a><br></div><div>Host of <a href="http://www.meetup.com/techlifecolumbus/" target="_blank">Columbus Code Jam</a> - <a href="https://www.twitter.com/CodeJamCMH" target="_blank">@CodeJamCMH</a></div><div><div>Curator of <a href="http://www.lenjaffe.com/AdventPlanet/" target="_blank">Advent Planet</a> - An Aggregation of Online Advent Calendars.<div><br></div></div></div></div></div></div></div>
</div></div></div></div>
<br>_______________________________________________<br>
List: <a href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class" target="_blank">http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class</a><br>
IRC: <a href="http://irc.perl.org#dbix-class" target="_blank">irc.perl.org#dbix-class</a><br>
SVN: <a href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/" target="_blank">http://dev.catalyst.perl.org/repos/bast/DBIx-Class/</a><br>
Searchable Archive: <a href="http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk" target="_blank">http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk</a><br></blockquote></div><br><br clear="all"><div><br></div>-- <br><div><b style="font-family:Times"><font size="1"><span style="font-family:Arial;font-weight:normal;vertical-align:baseline;white-space:pre-wrap">Kevin Karabian</span><span style="font-family:Arial;font-weight:normal;vertical-align:baseline;white-space:pre-wrap"></span><br><span style="font-family:Arial;font-weight:normal;vertical-align:baseline;white-space:pre-wrap">Senior Engineer</span><br><span style="font-family:Arial;font-weight:normal;vertical-align:baseline;white-space:pre-wrap">Turnitin – </span><a href="http://www.turnitin.com/" target="_blank"><span style="font-family:Arial;color:rgb(0,0,255);font-weight:normal;vertical-align:baseline;white-space:pre-wrap">www.turnitin.com</span></a><span style="font-family:Arial;font-weight:normal;vertical-align:baseline;white-space:pre-wrap"></span><br><span style="font-family:Arial;color:rgb(0,0,255);font-weight:normal;vertical-align:baseline;white-space:pre-wrap">kkarabian<a href="mailto:jdoe@turnitin.com" target="_blank">@turnitin.com</a></span><span style="font-family:Arial;font-weight:normal;vertical-align:baseline;white-space:pre-wrap"></span><br><span style="font-family:Arial;font-weight:normal;vertical-align:baseline;white-space:pre-wrap"><a href="tel:510.764.7529" value="+15107647529" target="_blank">510.764.7529</a></span></font></b></div>
</div></div></div></div></div>
<br>_______________________________________________<br>
List: <a href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class" target="_blank">http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class</a><br>
IRC: <a href="http://irc.perl.org#dbix-class" target="_blank">irc.perl.org#dbix-class</a><br>
SVN: <a href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/" target="_blank">http://dev.catalyst.perl.org/repos/bast/DBIx-Class/</a><br>
Searchable Archive: <a href="http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk" target="_blank">http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk</a><br></blockquote></div><br><br clear="all"><div><br></div>-- <br><div class="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div>Len Jaffe - Information Technology Smoke Jumper - <a href="mailto:lenjaffe@jaffesystems.com" target="_blank">lenjaffe@jaffesystems.com</a> </div><div>614-404-4214 <a href="https://www.twitter.com/lenJaffe" target="_blank">@LenJaffe</a> <a href="http://www.lenjaffe.com/" target="_blank">www.lenjaffe.com</a><br></div><div>Host of <a href="http://www.meetup.com/techlifecolumbus/" target="_blank">Columbus Code Jam</a> - <a href="https://www.twitter.com/CodeJamCMH" target="_blank">@CodeJamCMH</a></div><div><div>Curator of <a href="http://www.lenjaffe.com/AdventPlanet/" target="_blank">Advent Planet</a> - An Aggregation of Online Advent Calendars.<div><br></div></div></div></div></div></div></div>
</div>