[Dbix-class] don't want to hit the db again

[Dmitry L.]

Try set_cache.

$row->related_resultset($relationship_name)->set_cache( [ $related1,
$related2, ... ] );

Note, that for single-object relations (belongs_to, has_one, etc) you
also should provide array in set_cache.

On 29 April 2015 at 02:16, Kevin Karabian <kkarabian at 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 <lenjaffe at 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 <kkarabian at 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
>>> <ilmari at ilmari.org> wrote:
>>>>
>>>> Kevin Karabian <kkarabian at 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 Karabian
>>> Senior Engineer
>>> Turnitin – www.turnitin.com
>>> kkarabian at turnitin.com
>>> 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 -
>> lenjaffe at jaffesystems.com
>> 614-404-4214    @LenJaffe  www.lenjaffe.com
>> Host of Columbus Code Jam  - @CodeJamCMH
>> Curator of Advent Planet - 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 Karabian
> Senior Engineer
> Turnitin – www.turnitin.com
> kkarabian at turnitin.com
> 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



-- 
//wbr, Dmitry L.