<div dir="ltr">Thanks so much! This will come in very handy in many many places ahead.<div><br></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Tue, Apr 28, 2015 at 4:28 PM, Dmitry L. <span dir="ltr"><<a href="mailto:dim0xff@gmail.com" target="_blank">dim0xff@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Try set_cache.<br>
<br>
$row->related_resultset($relationship_name)->set_cache( [ $related1,<br>
$related2, ... ] );<br>
<br>
Note, that for single-object relations (belongs_to, has_one, etc) you<br>
also should provide array in set_cache.<br>
<div><div class="h5"><br>
On 29 April 2015 at 02:16, Kevin Karabian <<a href="mailto:kkarabian@turnitin.com">kkarabian@turnitin.com</a>> wrote:<br>
> It is not about execution time, but data size. The dba's don't like it when<br>
> I tell them I am pulling x times more data than before.<br>
><br>
> I am not against prefetching. In fact, I had it as a prefetch, but, they<br>
> balked about the potential increase in data returned.<br>
><br>
><br>
> On Tue, Apr 28, 2015 at 3:51 PM, Len Jaffe <<a href="mailto:lenjaffe@jaffesystems.com">lenjaffe@jaffesystems.com</a>><br>
> wrote:<br>
>><br>
>> Prefetch generates SQL joins. The join is the FUNDAMENTAL unit of work in<br>
>> a relational database. In 25 years, the only time I've ever seen a join not<br>
>> be faster than decomposing into multiple queries is when the queries involve<br>
>> millions of rows in one or more tables, and/or when the RDBMs query<br>
>> optimizer ran into a pathological case, or a bug.<br>
>><br>
>> Try an experiment. Benchmark the query using a column-specified prefetch,<br>
>> and then benchmark running the first query, extracting the list of foreign<br>
>> keys, and runnign a second query to return all of those. I'd like to see the<br>
>> difference.<br>
>><br>
>> I predict that for 100 records, the difference in execution time will be<br>
>> below the threshold where it makes any sense to optimize the join away.<br>
>><br>
>> Len.<br>
>><br>
>><br>
>><br>
>><br>
>> On Tue, Apr 28, 2015 at 6:37 PM, Kevin Karabian <<a href="mailto:kkarabian@turnitin.com">kkarabian@turnitin.com</a>><br>
>> wrote:<br>
>>><br>
>>> Yes, but the problem is for a has_many, prefetch causes multiple rows to<br>
>>> be returned. For example if a result object has 10 related objects then 10<br>
>>> rows are returned for that one object. If the result object is large, then<br>
>>> that is a lot of repetition. I want to basically run a query that will<br>
>>> return only the 10 related objects and then put those into the result object<br>
>>> eliminating the repetition.<br>
>>><br>
>>> And actually my use case is that I want to do this for a large number or<br>
>>> result objects so I am amortizing the cost by grabbing all the related<br>
>>> objects for all the result objects in question. So say, I have 10 objects<br>
>>> and each has 10 related objects. I get the 100 related objects at once and<br>
>>> populate the result objects with the relationship data.<br>
>>><br>
>>> On Tue, Apr 28, 2015 at 3:27 PM, Dagfinn Ilmari Mannsåker<br>
>>> <<a href="mailto:ilmari@ilmari.org">ilmari@ilmari.org</a>> wrote:<br>
>>>><br>
>>>> Kevin Karabian <<a href="mailto:kkarabian@turnitin.com">kkarabian@turnitin.com</a>> writes:<br>
>>>><br>
>>>> > Hi,<br>
>>>> ><br>
>>>> > Is there a way to store already retrieved related objects (related as<br>
>>>> > 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>
>>>> 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>
>>>><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:<br>
>>>> <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>
>>><br>
>>><br>
>>><br>
>>><br>
>>> --<br>
>>> Kevin Karabian<br>
>>> Senior Engineer<br>
>>> Turnitin – <a href="http://www.turnitin.com" target="_blank">www.turnitin.com</a><br>
>>> <a href="mailto:kkarabian@turnitin.com">kkarabian@turnitin.com</a><br>
>>> <a href="tel:510.764.7529" value="+15107647529">510.764.7529</a><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:<br>
>>> <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>
>><br>
>><br>
>><br>
>><br>
>> --<br>
>> Len Jaffe - Information Technology Smoke Jumper -<br>
>> <a href="mailto:lenjaffe@jaffesystems.com">lenjaffe@jaffesystems.com</a><br>
>> <a href="tel:614-404-4214" value="+16144044214">614-404-4214</a> @LenJaffe <a href="http://www.lenjaffe.com" target="_blank">www.lenjaffe.com</a><br>
>> Host of Columbus Code Jam - @CodeJamCMH<br>
>> Curator of Advent Planet - An Aggregation of Online Advent Calendars.<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:<br>
>> <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>
><br>
><br>
><br>
><br>
> --<br>
> Kevin Karabian<br>
> Senior Engineer<br>
> Turnitin – <a href="http://www.turnitin.com" target="_blank">www.turnitin.com</a><br>
> <a href="mailto:kkarabian@turnitin.com">kkarabian@turnitin.com</a><br>
> <a href="tel:510.764.7529" value="+15107647529">510.764.7529</a><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:<br>
> <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>
<br>
<br>
<br>
--<br>
</div></div>//wbr, Dmitry L.<br>
<div class="HOEnZb"><div class="h5"><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></div></div></blockquote></div><br><br clear="all"><div><br></div>-- <br><div class="gmail_signature"><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">510.764.7529</span></font></b></div>
</div>