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

Len Jaffe lenjaffe at jaffesystems.com
Tue Apr 28 23:30:58 GMT 2015


> The dba's don't like it when I tell them I am pulling x times more data
than before.
DBAs: This is why we can't have nice things.

So then just do two queries, perform in memory look-ups from one list of
records into the other.



On Tue, Apr 28, 2015 at 7:16 PM, 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 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>*
>
> _______________________________________________
> 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/3134dc3b/attachment.htm>


More information about the DBIx-Class mailing list