Mailing-List: contact dev-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@cayenne.apache.org
Received-SPF: pass (nike.apache.org: local policy includes SPF record at
 spf.trusted-forwarder.org)
Message-ID: <52B123E3.2020006@maniatis.org>
Date: Wed, 18 Dec 2013 15:26:11 +1100
From: Aristedes Maniatis <ari@maniatis.org>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9;
 rv:26.0) Gecko/20100101 Thunderbird/26.0
MIME-Version: 1.0
To: dev@cayenne.apache.org
Subject: Re: cayenne performance tuning issues
References: 
 <CADSaZMgMJpBPK2khS1x_AB6HnkSH1kN=3OZmpwhy5po48tPDgQ@mail.gmail.com>
	<52B11B5E.7070403@maniatis.org>
 <CADSaZMhvvZFHAnoaAysCg+fGquD4vJtogkAFthqNPKgka2x+dg@mail.gmail.com>
In-Reply-To: 
 <CADSaZMhvvZFHAnoaAysCg+fGquD4vJtogkAFthqNPKgka2x+dg@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Prefetching is not supposed to change the records that you return from the query in the primary table. Which is why a left join is appropriate. An inner join would suppress records in the primary table without a relation to the second table.

That might be what you want, but that isn't the point of prefetching which is purely to improve performance of otherwise working code.

At any rate, your 30 second SQL query isn't anything to do with prefetching. As Mike pointed out, you need to understand why this query is taking so long and you might like to execute it directly in your database or execute it with the word "EXPLAIN" before the select query.

Ari


On 18/12/2013 3:09pm, Michael Hatch wrote:
> I did testing with default prefetching disjoint joint semantics and joint
> prefetching. As you stated, disjoint sends multiple SQL requests. I'm not
> sure which scenario that would be preferred. Joint semantics seems to
> create left joins, although I'd want inner joins. Is there an option to
> prefetch and inner join? Didn't look to be the case
> On Dec 17, 2013 9:50 PM, "Aristedes Maniatis" <ari@maniatis.org> wrote:
> 
>> On 18/12/2013 4:29am, Michael Hatch wrote:
>>
>>> The first sql statement is fetching the user info, while the second sql
>> is
>>> for inflating the roles relationship. I understand the documentation
>>> explicitly states that prefetching will inflate relationships for you,
>> but
>>> it still does so via multiple sql commands per relationship, instead of
>> via
>>> a single sql statement with an inner join. Did I simply do something
>> wrong
>>> somewhere?
>>
>> Cayenne will automatically decide whether to issue multiple SQL requests
>> (disjoint prefetch) or one big SQL statement which captures all the data
>> (joint). Don't assume without testing which one will be better for your
>> needs, and Cayenne does allow you to choose which sementics to use. But
>> SelectQuery will be disjoint by default.
>>
>> Imagine for a moment: to-many joins to 5 tables from a source table on
>> which you want to use a joint prefetch. You can very quickly return a vast
>> number of rows using an outer join in this way.
>>
>> Ari
>>
>>
>> --
>> -------------------------->
>> Aristedes Maniatis
>> GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A
>>
> 

-- 
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A