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

[Len Jaffe]

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 KarabianSenior EngineerTurnitin – www.turnitin.com
> <http://www.turnitin.com/>kkarabian at turnitin.com
> <jdoe at 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 - lenjaffe at 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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20150428/d19b786b/attachment.htm>