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

Kevin Karabian kkarabian at turnitin.com
Wed Apr 29 01:13:13 GMT 2015


Thanks so much!  This will come in very handy in many many places ahead.


On Tue, Apr 28, 2015 at 4:28 PM, Dmitry L. <dim0xff at gmail.com> wrote:

> 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.
>
> _______________________________________________
> 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*
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20150428/d075aacf/attachment-0001.htm>


More information about the DBIx-Class mailing list