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

[Kevin Karabian]

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