You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Nick Johnson <ap...@spatula.net> on 2007/11/15 06:46:17 UTC
how to avoid large number of queries?
In general, what are some ways to avoid a large number of SQL queries
being issued for one JPA query? In the case I'm faced with at present, it
isn't an issue of lazy loading, because all of the queries run before any
of the objects in the result set are ever accessed.
It just seems like for whatever reason, OpenJPA doesn't believe it can get
all of the information it needs via one query, even though I know for
certain it's possible (because I used to do it that way). I suspect there
is probably a way to restructure the query or tweak the model to persuade
OpenJPA to do a more efficient query.
I can't really relate the whole model very easily in a short email, but I
have Messages, MessageRoots and Articles. A MessageRoot has many
Messages. Both Messages and Articles join 1:1 to Account. My query looks
like this:
Select message, article from Message message, Article article where
message.createDate > ?1
and message.root.sourceId = article.objectId
and message.pending = false
order by article.objectId desc, message.objectId desc
The query strategy seemed to be selecting out the object IDs of all of the
Messages and Articles first, and then issuing a select for every one of
those object IDs.
There's no direct or indirect relationship between a Message and an
Article... the Message stuff exists independently of all my other
Entities, but any of them can link up by creating a MessageRoot with a
sourceId = their primary key. It wouldn't surprise me if this were part
of the trouble.
The details follow below... what might I try to get a better query
strategy?
First OpenJPA issues this query:
SELECT t0.object_id, t3.object_id
FROM Message t0
INNER JOIN message_root t1 ON t0.root_id = t1.object_id
INNER JOIN message_root t2 ON t0.root_id = t2.object_id
CROSS JOIN Article t3
WHERE (t0.create_date > ? AND t2.source_id = t3.object_id
AND t0.pending = ?)
ORDER BY t3.object_id
DESC, t0.object_id DESC [params=(Timestamp) 2007-11-11 21:28:49.97,
(boolean) false]
Then it issues a query like this for every row in the result set:
SELECT t0.version, t1.object_id, t1.autologin_id, t1.change_summary,
t1.changer, t1.create_date, t1.crypt_password, t1.email, t1.has_avatar,
t1.is_admin, t1.is_editor, t1.is_moderator, t1.is_okay_to_post,
t1.is_okay_to_submit, t1.is_owner, t1.location, t1.password_answer,
t1.password_question, t1.premium_until, t1.uid, t1.username, t1.verified,
t1.version, t0.alias, t0.change_summary, t0.changer, t0.create_date,
t0.fuzzy_md5_1, t0.fuzzy_md5_2, t0.ip, t0.is_closed, t0.is_registered,
t0.location, t0.md5, t0.message_text, t0.parent_id, t0.pending,
t2.object_id, t2.last_change, t2.last_post, t2.post_count,
t2.post_count_24hr, t2.posting_permitted, t2.source_id, t2.source_type,
t0.uid, t0.user_class
FROM Message t0
INNER JOIN Account t1 ON t0.account_id = t1.object_id
LEFT OUTER JOIN message_root t2 ON t0.root_id = t2.object_id
WHERE t0.object_id = ? [params=(long) 160497]
(it also issues a similar query to fill in the Articles)
--
"Courage isn't just a matter of not being frightened, you know. It's being
afraid and doing what you have to do anyway."
Doctor Who - Planet of the Daleks
This message has been brought to you by Nick Johnson 2.3b1 and the number 6.
http://healerNick.com/ http://morons.org/ http://spatula.net/
Re: how to avoid large number of queries?
Posted by Patrick Linskey <pl...@gmail.com>.
Parallel should be doing a single query for each separate relation,
not for each instance. It sounds like you are seeing a query per
instance, right?
Can you post just the domain model so we can look at the relations in
more detail?
-Patrick
On Nov 18, 2007 6:31 PM, Nick Johnson <ap...@spatula.net> wrote:
> I'll see what I can do... it's a fairly complicated mess.
>
> One thing I did notice is that the behaviour sounds exactly like what's
> described by the "parallel" eager fetch mode (as opposed to the "join"
> mode), but my attempts to change to the "join" fetch mode are ignored, at
> least for this query, even if I set the property in persistence.xml.
>
> Nick
>
>
> On Sun, 18 Nov 2007, Patrick Linskey wrote:
>
> > Is it possible to upload a test case that demonstrates the problem?
> >
> > -Patrick
> >
> > On Nov 18, 2007 6:06 PM, Nick Johnson <ap...@spatula.net> wrote:
> > > Unfortunately, my hypoethesis seems to be incorrect... even if I turn off
> > > caching, OpenJPA still performs one query to get the object ids and a
> > > separate query for every one of those object ids to populate the objects.
> > >
> > > Again, this happen before a single record is ever examined from the result
> > > list, so I don't think it's a lazy loading issue.
> > >
> > > All of these objects could be fetched with a single query, which is what
> > > I'd really rather have OpenJPA do, but cannot seem to find a way to
> > > accomplish.
> > >
> > > Nick
> > >
> > >
> > > On Fri, 16 Nov 2007, Nick Johnson wrote:
> > >
> > > > Just a thought here... is OpenJPA using this strategy because it's
> > > > optimized to hope that objects will already be present in cache? That
> > > > would explain the behaviour anyway... if the objects were in cache, it
> > > > would be more efficient to select just the primary keys and then pull the
> > > > already-populated entites out of cache rather than run a more expensive
> > > > query to pull everything out of the database.
> > > >
> > > > If this hypothesis is correct, is there a way to tune this behaviour?
> > > >
> > > > On Wed, 14 Nov 2007, Nick Johnson wrote:
> > > > [snip]
> > > > > The query strategy seemed to be selecting out the object IDs of all of the
> > > > > Messages and Articles first, and then issuing a select for every one of
> > > > > those object IDs.
> > > > [snip]
> > > > > The details follow below... what might I try to get a better query
> > > > > strategy?
> > > > >
> > > > > First OpenJPA issues this query:
> > > > >
> > > > > SELECT t0.object_id, t3.object_id
> > > > > FROM Message t0
> > > > > INNER JOIN message_root t1 ON t0.root_id = t1.object_id
> > > > > INNER JOIN message_root t2 ON t0.root_id = t2.object_id
> > > > > CROSS JOIN Article t3
> > > > > WHERE (t0.create_date > ? AND t2.source_id = t3.object_id
> > > > > AND t0.pending = ?)
> > > > > ORDER BY t3.object_id
> > > > > DESC, t0.object_id DESC [params=(Timestamp) 2007-11-11 21:28:49.97,
> > > > > (boolean) false]
> > > > >
> > > > > Then it issues a query like this for every row in the result set:
> > > > >
> > > > > SELECT t0.version, t1.object_id, t1.autologin_id, t1.change_summary,
> > > > > t1.changer, t1.create_date, t1.crypt_password, t1.email, t1.has_avatar,
> > > > > t1.is_admin, t1.is_editor, t1.is_moderator, t1.is_okay_to_post,
> > > > > t1.is_okay_to_submit, t1.is_owner, t1.location, t1.password_answer,
> > > > > t1.password_question, t1.premium_until, t1.uid, t1.username, t1.verified,
> > > > > t1.version, t0.alias, t0.change_summary, t0.changer, t0.create_date,
> > > > > t0.fuzzy_md5_1, t0.fuzzy_md5_2, t0.ip, t0.is_closed, t0.is_registered,
> > > > > t0.location, t0.md5, t0.message_text, t0.parent_id, t0.pending,
> > > > > t2.object_id, t2.last_change, t2.last_post, t2.post_count,
> > > > > t2.post_count_24hr, t2.posting_permitted, t2.source_id, t2.source_type,
> > > > > t0.uid, t0.user_class
> > > > > FROM Message t0
> > > > > INNER JOIN Account t1 ON t0.account_id = t1.object_id
> > > > > LEFT OUTER JOIN message_root t2 ON t0.root_id = t2.object_id
> > > > > WHERE t0.object_id = ? [params=(long) 160497]
> > > > >
> > > > > (it also issues a similar query to fill in the Articles)
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > > --
> > > "Courage isn't just a matter of not being frightened, you know. It's being
> > > afraid and doing what you have to do anyway."
> > > Doctor Who - Planet of the Daleks
> > > This message has been brought to you by Nick Johnson 2.3b1 and the number 6.
> > > http://healerNick.com/ http://morons.org/ http://spatula.net/
> > >
> >
> >
> >
> >
>
> --
> "Courage isn't just a matter of not being frightened, you know. It's being
> afraid and doing what you have to do anyway."
> Doctor Who - Planet of the Daleks
> This message has been brought to you by Nick Johnson 2.3b1 and the number 6.
> http://healerNick.com/ http://morons.org/ http://spatula.net/
>
--
Patrick Linskey
202 669 5907
Re: how to avoid large number of queries?
Posted by Nick Johnson <ap...@spatula.net>.
I'll see what I can do... it's a fairly complicated mess.
One thing I did notice is that the behaviour sounds exactly like what's
described by the "parallel" eager fetch mode (as opposed to the "join"
mode), but my attempts to change to the "join" fetch mode are ignored, at
least for this query, even if I set the property in persistence.xml.
Nick
On Sun, 18 Nov 2007, Patrick Linskey wrote:
> Is it possible to upload a test case that demonstrates the problem?
>
> -Patrick
>
> On Nov 18, 2007 6:06 PM, Nick Johnson <ap...@spatula.net> wrote:
> > Unfortunately, my hypoethesis seems to be incorrect... even if I turn off
> > caching, OpenJPA still performs one query to get the object ids and a
> > separate query for every one of those object ids to populate the objects.
> >
> > Again, this happen before a single record is ever examined from the result
> > list, so I don't think it's a lazy loading issue.
> >
> > All of these objects could be fetched with a single query, which is what
> > I'd really rather have OpenJPA do, but cannot seem to find a way to
> > accomplish.
> >
> > Nick
> >
> >
> > On Fri, 16 Nov 2007, Nick Johnson wrote:
> >
> > > Just a thought here... is OpenJPA using this strategy because it's
> > > optimized to hope that objects will already be present in cache? That
> > > would explain the behaviour anyway... if the objects were in cache, it
> > > would be more efficient to select just the primary keys and then pull the
> > > already-populated entites out of cache rather than run a more expensive
> > > query to pull everything out of the database.
> > >
> > > If this hypothesis is correct, is there a way to tune this behaviour?
> > >
> > > On Wed, 14 Nov 2007, Nick Johnson wrote:
> > > [snip]
> > > > The query strategy seemed to be selecting out the object IDs of all of the
> > > > Messages and Articles first, and then issuing a select for every one of
> > > > those object IDs.
> > > [snip]
> > > > The details follow below... what might I try to get a better query
> > > > strategy?
> > > >
> > > > First OpenJPA issues this query:
> > > >
> > > > SELECT t0.object_id, t3.object_id
> > > > FROM Message t0
> > > > INNER JOIN message_root t1 ON t0.root_id = t1.object_id
> > > > INNER JOIN message_root t2 ON t0.root_id = t2.object_id
> > > > CROSS JOIN Article t3
> > > > WHERE (t0.create_date > ? AND t2.source_id = t3.object_id
> > > > AND t0.pending = ?)
> > > > ORDER BY t3.object_id
> > > > DESC, t0.object_id DESC [params=(Timestamp) 2007-11-11 21:28:49.97,
> > > > (boolean) false]
> > > >
> > > > Then it issues a query like this for every row in the result set:
> > > >
> > > > SELECT t0.version, t1.object_id, t1.autologin_id, t1.change_summary,
> > > > t1.changer, t1.create_date, t1.crypt_password, t1.email, t1.has_avatar,
> > > > t1.is_admin, t1.is_editor, t1.is_moderator, t1.is_okay_to_post,
> > > > t1.is_okay_to_submit, t1.is_owner, t1.location, t1.password_answer,
> > > > t1.password_question, t1.premium_until, t1.uid, t1.username, t1.verified,
> > > > t1.version, t0.alias, t0.change_summary, t0.changer, t0.create_date,
> > > > t0.fuzzy_md5_1, t0.fuzzy_md5_2, t0.ip, t0.is_closed, t0.is_registered,
> > > > t0.location, t0.md5, t0.message_text, t0.parent_id, t0.pending,
> > > > t2.object_id, t2.last_change, t2.last_post, t2.post_count,
> > > > t2.post_count_24hr, t2.posting_permitted, t2.source_id, t2.source_type,
> > > > t0.uid, t0.user_class
> > > > FROM Message t0
> > > > INNER JOIN Account t1 ON t0.account_id = t1.object_id
> > > > LEFT OUTER JOIN message_root t2 ON t0.root_id = t2.object_id
> > > > WHERE t0.object_id = ? [params=(long) 160497]
> > > >
> > > > (it also issues a similar query to fill in the Articles)
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> > --
> > "Courage isn't just a matter of not being frightened, you know. It's being
> > afraid and doing what you have to do anyway."
> > Doctor Who - Planet of the Daleks
> > This message has been brought to you by Nick Johnson 2.3b1 and the number 6.
> > http://healerNick.com/ http://morons.org/ http://spatula.net/
> >
>
>
>
>
--
"Courage isn't just a matter of not being frightened, you know. It's being
afraid and doing what you have to do anyway."
Doctor Who - Planet of the Daleks
This message has been brought to you by Nick Johnson 2.3b1 and the number 6.
http://healerNick.com/ http://morons.org/ http://spatula.net/
Re: how to avoid large number of queries?
Posted by Patrick Linskey <pl...@gmail.com>.
Is it possible to upload a test case that demonstrates the problem?
-Patrick
On Nov 18, 2007 6:06 PM, Nick Johnson <ap...@spatula.net> wrote:
> Unfortunately, my hypoethesis seems to be incorrect... even if I turn off
> caching, OpenJPA still performs one query to get the object ids and a
> separate query for every one of those object ids to populate the objects.
>
> Again, this happen before a single record is ever examined from the result
> list, so I don't think it's a lazy loading issue.
>
> All of these objects could be fetched with a single query, which is what
> I'd really rather have OpenJPA do, but cannot seem to find a way to
> accomplish.
>
> Nick
>
>
> On Fri, 16 Nov 2007, Nick Johnson wrote:
>
> > Just a thought here... is OpenJPA using this strategy because it's
> > optimized to hope that objects will already be present in cache? That
> > would explain the behaviour anyway... if the objects were in cache, it
> > would be more efficient to select just the primary keys and then pull the
> > already-populated entites out of cache rather than run a more expensive
> > query to pull everything out of the database.
> >
> > If this hypothesis is correct, is there a way to tune this behaviour?
> >
> > On Wed, 14 Nov 2007, Nick Johnson wrote:
> > [snip]
> > > The query strategy seemed to be selecting out the object IDs of all of the
> > > Messages and Articles first, and then issuing a select for every one of
> > > those object IDs.
> > [snip]
> > > The details follow below... what might I try to get a better query
> > > strategy?
> > >
> > > First OpenJPA issues this query:
> > >
> > > SELECT t0.object_id, t3.object_id
> > > FROM Message t0
> > > INNER JOIN message_root t1 ON t0.root_id = t1.object_id
> > > INNER JOIN message_root t2 ON t0.root_id = t2.object_id
> > > CROSS JOIN Article t3
> > > WHERE (t0.create_date > ? AND t2.source_id = t3.object_id
> > > AND t0.pending = ?)
> > > ORDER BY t3.object_id
> > > DESC, t0.object_id DESC [params=(Timestamp) 2007-11-11 21:28:49.97,
> > > (boolean) false]
> > >
> > > Then it issues a query like this for every row in the result set:
> > >
> > > SELECT t0.version, t1.object_id, t1.autologin_id, t1.change_summary,
> > > t1.changer, t1.create_date, t1.crypt_password, t1.email, t1.has_avatar,
> > > t1.is_admin, t1.is_editor, t1.is_moderator, t1.is_okay_to_post,
> > > t1.is_okay_to_submit, t1.is_owner, t1.location, t1.password_answer,
> > > t1.password_question, t1.premium_until, t1.uid, t1.username, t1.verified,
> > > t1.version, t0.alias, t0.change_summary, t0.changer, t0.create_date,
> > > t0.fuzzy_md5_1, t0.fuzzy_md5_2, t0.ip, t0.is_closed, t0.is_registered,
> > > t0.location, t0.md5, t0.message_text, t0.parent_id, t0.pending,
> > > t2.object_id, t2.last_change, t2.last_post, t2.post_count,
> > > t2.post_count_24hr, t2.posting_permitted, t2.source_id, t2.source_type,
> > > t0.uid, t0.user_class
> > > FROM Message t0
> > > INNER JOIN Account t1 ON t0.account_id = t1.object_id
> > > LEFT OUTER JOIN message_root t2 ON t0.root_id = t2.object_id
> > > WHERE t0.object_id = ? [params=(long) 160497]
> > >
> > > (it also issues a similar query to fill in the Articles)
> > >
> > >
> > >
> > >
> >
> >
>
> --
> "Courage isn't just a matter of not being frightened, you know. It's being
> afraid and doing what you have to do anyway."
> Doctor Who - Planet of the Daleks
> This message has been brought to you by Nick Johnson 2.3b1 and the number 6.
> http://healerNick.com/ http://morons.org/ http://spatula.net/
>
--
Patrick Linskey
202 669 5907
Re: how to avoid large number of queries?
Posted by Nick Johnson <ap...@spatula.net>.
Unfortunately, my hypoethesis seems to be incorrect... even if I turn off
caching, OpenJPA still performs one query to get the object ids and a
separate query for every one of those object ids to populate the objects.
Again, this happen before a single record is ever examined from the result
list, so I don't think it's a lazy loading issue.
All of these objects could be fetched with a single query, which is what
I'd really rather have OpenJPA do, but cannot seem to find a way to
accomplish.
Nick
On Fri, 16 Nov 2007, Nick Johnson wrote:
> Just a thought here... is OpenJPA using this strategy because it's
> optimized to hope that objects will already be present in cache? That
> would explain the behaviour anyway... if the objects were in cache, it
> would be more efficient to select just the primary keys and then pull the
> already-populated entites out of cache rather than run a more expensive
> query to pull everything out of the database.
>
> If this hypothesis is correct, is there a way to tune this behaviour?
>
> On Wed, 14 Nov 2007, Nick Johnson wrote:
> [snip]
> > The query strategy seemed to be selecting out the object IDs of all of the
> > Messages and Articles first, and then issuing a select for every one of
> > those object IDs.
> [snip]
> > The details follow below... what might I try to get a better query
> > strategy?
> >
> > First OpenJPA issues this query:
> >
> > SELECT t0.object_id, t3.object_id
> > FROM Message t0
> > INNER JOIN message_root t1 ON t0.root_id = t1.object_id
> > INNER JOIN message_root t2 ON t0.root_id = t2.object_id
> > CROSS JOIN Article t3
> > WHERE (t0.create_date > ? AND t2.source_id = t3.object_id
> > AND t0.pending = ?)
> > ORDER BY t3.object_id
> > DESC, t0.object_id DESC [params=(Timestamp) 2007-11-11 21:28:49.97,
> > (boolean) false]
> >
> > Then it issues a query like this for every row in the result set:
> >
> > SELECT t0.version, t1.object_id, t1.autologin_id, t1.change_summary,
> > t1.changer, t1.create_date, t1.crypt_password, t1.email, t1.has_avatar,
> > t1.is_admin, t1.is_editor, t1.is_moderator, t1.is_okay_to_post,
> > t1.is_okay_to_submit, t1.is_owner, t1.location, t1.password_answer,
> > t1.password_question, t1.premium_until, t1.uid, t1.username, t1.verified,
> > t1.version, t0.alias, t0.change_summary, t0.changer, t0.create_date,
> > t0.fuzzy_md5_1, t0.fuzzy_md5_2, t0.ip, t0.is_closed, t0.is_registered,
> > t0.location, t0.md5, t0.message_text, t0.parent_id, t0.pending,
> > t2.object_id, t2.last_change, t2.last_post, t2.post_count,
> > t2.post_count_24hr, t2.posting_permitted, t2.source_id, t2.source_type,
> > t0.uid, t0.user_class
> > FROM Message t0
> > INNER JOIN Account t1 ON t0.account_id = t1.object_id
> > LEFT OUTER JOIN message_root t2 ON t0.root_id = t2.object_id
> > WHERE t0.object_id = ? [params=(long) 160497]
> >
> > (it also issues a similar query to fill in the Articles)
> >
> >
> >
> >
>
>
--
"Courage isn't just a matter of not being frightened, you know. It's being
afraid and doing what you have to do anyway."
Doctor Who - Planet of the Daleks
This message has been brought to you by Nick Johnson 2.3b1 and the number 6.
http://healerNick.com/ http://morons.org/ http://spatula.net/
Re: how to avoid large number of queries?
Posted by Nick Johnson <ap...@spatula.net>.
That was my initial thinking, but it's doing it before I ever even begin
to traverse the result list, and the relation between Message and Account
is a 1:1.
Nick
On Fri, 16 Nov 2007, plinskey@gmail.com wrote:
> Hi,
>
> It sounds like your relations are lazy (the default for collections
> and maps). What happens if you change them to be eager?
>
> -Patrick
>
> On 11/16/07, Nick Johnson <ap...@spatula.net> wrote:
> > Just a thought here... is OpenJPA using this strategy because it's
> > optimized to hope that objects will already be present in cache? That
> > would explain the behaviour anyway... if the objects were in cache, it
> > would be more efficient to select just the primary keys and then pull the
> > already-populated entites out of cache rather than run a more expensive
> > query to pull everything out of the database.
> >
> > If this hypothesis is correct, is there a way to tune this behaviour?
> >
> > On Wed, 14 Nov 2007, Nick Johnson wrote:
> > [snip]
> > > The query strategy seemed to be selecting out the object IDs of all of the
> > > Messages and Articles first, and then issuing a select for every one of
> > > those object IDs.
> > [snip]
> > > The details follow below... what might I try to get a better query
> > > strategy?
> > >
> > > First OpenJPA issues this query:
> > >
> > > SELECT t0.object_id, t3.object_id
> > > FROM Message t0
> > > INNER JOIN message_root t1 ON t0.root_id = t1.object_id
> > > INNER JOIN message_root t2 ON t0.root_id = t2.object_id
> > > CROSS JOIN Article t3
> > > WHERE (t0.create_date > ? AND t2.source_id = t3.object_id
> > > AND t0.pending = ?)
> > > ORDER BY t3.object_id
> > > DESC, t0.object_id DESC [params=(Timestamp) 2007-11-11 21:28:49.97,
> > > (boolean) false]
> > >
> > > Then it issues a query like this for every row in the result set:
> > >
> > > SELECT t0.version, t1.object_id, t1.autologin_id, t1.change_summary,
> > > t1.changer, t1.create_date, t1.crypt_password, t1.email, t1.has_avatar,
> > > t1.is_admin, t1.is_editor, t1.is_moderator, t1.is_okay_to_post,
> > > t1.is_okay_to_submit, t1.is_owner, t1.location, t1.password_answer,
> > > t1.password_question, t1.premium_until, t1.uid, t1.username, t1.verified,
> > > t1.version, t0.alias, t0.change_summary, t0.changer, t0.create_date,
> > > t0.fuzzy_md5_1, t0.fuzzy_md5_2, t0.ip, t0.is_closed, t0.is_registered,
> > > t0.location, t0.md5, t0.message_text, t0.parent_id, t0.pending,
> > > t2.object_id, t2.last_change, t2.last_post, t2.post_count,
> > > t2.post_count_24hr, t2.posting_permitted, t2.source_id, t2.source_type,
> > > t0.uid, t0.user_class
> > > FROM Message t0
> > > INNER JOIN Account t1 ON t0.account_id = t1.object_id
> > > LEFT OUTER JOIN message_root t2 ON t0.root_id = t2.object_id
> > > WHERE t0.object_id = ? [params=(long) 160497]
> > >
> > > (it also issues a similar query to fill in the Articles)
> > >
> > >
> > >
> > >
> >
> > --
> > "Courage isn't just a matter of not being frightened, you know. It's being
> > afraid and doing what you have to do anyway."
> > Doctor Who - Planet of the Daleks
> > This message has been brought to you by Nick Johnson 2.3b1 and the number 6.
> > http://healerNick.com/ http://morons.org/ http://spatula.net/
> >
>
>
>
--
"Courage isn't just a matter of not being frightened, you know. It's being
afraid and doing what you have to do anyway."
Doctor Who - Planet of the Daleks
This message has been brought to you by Nick Johnson 2.3b1 and the number 6.
http://healerNick.com/ http://morons.org/ http://spatula.net/
Re: how to avoid large number of queries?
Posted by pl...@gmail.com.
Hi,
It sounds like your relations are lazy (the default for collections
and maps). What happens if you change them to be eager?
-Patrick
On 11/16/07, Nick Johnson <ap...@spatula.net> wrote:
> Just a thought here... is OpenJPA using this strategy because it's
> optimized to hope that objects will already be present in cache? That
> would explain the behaviour anyway... if the objects were in cache, it
> would be more efficient to select just the primary keys and then pull the
> already-populated entites out of cache rather than run a more expensive
> query to pull everything out of the database.
>
> If this hypothesis is correct, is there a way to tune this behaviour?
>
> On Wed, 14 Nov 2007, Nick Johnson wrote:
> [snip]
> > The query strategy seemed to be selecting out the object IDs of all of the
> > Messages and Articles first, and then issuing a select for every one of
> > those object IDs.
> [snip]
> > The details follow below... what might I try to get a better query
> > strategy?
> >
> > First OpenJPA issues this query:
> >
> > SELECT t0.object_id, t3.object_id
> > FROM Message t0
> > INNER JOIN message_root t1 ON t0.root_id = t1.object_id
> > INNER JOIN message_root t2 ON t0.root_id = t2.object_id
> > CROSS JOIN Article t3
> > WHERE (t0.create_date > ? AND t2.source_id = t3.object_id
> > AND t0.pending = ?)
> > ORDER BY t3.object_id
> > DESC, t0.object_id DESC [params=(Timestamp) 2007-11-11 21:28:49.97,
> > (boolean) false]
> >
> > Then it issues a query like this for every row in the result set:
> >
> > SELECT t0.version, t1.object_id, t1.autologin_id, t1.change_summary,
> > t1.changer, t1.create_date, t1.crypt_password, t1.email, t1.has_avatar,
> > t1.is_admin, t1.is_editor, t1.is_moderator, t1.is_okay_to_post,
> > t1.is_okay_to_submit, t1.is_owner, t1.location, t1.password_answer,
> > t1.password_question, t1.premium_until, t1.uid, t1.username, t1.verified,
> > t1.version, t0.alias, t0.change_summary, t0.changer, t0.create_date,
> > t0.fuzzy_md5_1, t0.fuzzy_md5_2, t0.ip, t0.is_closed, t0.is_registered,
> > t0.location, t0.md5, t0.message_text, t0.parent_id, t0.pending,
> > t2.object_id, t2.last_change, t2.last_post, t2.post_count,
> > t2.post_count_24hr, t2.posting_permitted, t2.source_id, t2.source_type,
> > t0.uid, t0.user_class
> > FROM Message t0
> > INNER JOIN Account t1 ON t0.account_id = t1.object_id
> > LEFT OUTER JOIN message_root t2 ON t0.root_id = t2.object_id
> > WHERE t0.object_id = ? [params=(long) 160497]
> >
> > (it also issues a similar query to fill in the Articles)
> >
> >
> >
> >
>
> --
> "Courage isn't just a matter of not being frightened, you know. It's being
> afraid and doing what you have to do anyway."
> Doctor Who - Planet of the Daleks
> This message has been brought to you by Nick Johnson 2.3b1 and the number 6.
> http://healerNick.com/ http://morons.org/ http://spatula.net/
>
--
Patrick Linskey
202 669 5907
Re: how to avoid large number of queries?
Posted by Nick Johnson <ap...@spatula.net>.
Just a thought here... is OpenJPA using this strategy because it's
optimized to hope that objects will already be present in cache? That
would explain the behaviour anyway... if the objects were in cache, it
would be more efficient to select just the primary keys and then pull the
already-populated entites out of cache rather than run a more expensive
query to pull everything out of the database.
If this hypothesis is correct, is there a way to tune this behaviour?
On Wed, 14 Nov 2007, Nick Johnson wrote:
[snip]
> The query strategy seemed to be selecting out the object IDs of all of the
> Messages and Articles first, and then issuing a select for every one of
> those object IDs.
[snip]
> The details follow below... what might I try to get a better query
> strategy?
>
> First OpenJPA issues this query:
>
> SELECT t0.object_id, t3.object_id
> FROM Message t0
> INNER JOIN message_root t1 ON t0.root_id = t1.object_id
> INNER JOIN message_root t2 ON t0.root_id = t2.object_id
> CROSS JOIN Article t3
> WHERE (t0.create_date > ? AND t2.source_id = t3.object_id
> AND t0.pending = ?)
> ORDER BY t3.object_id
> DESC, t0.object_id DESC [params=(Timestamp) 2007-11-11 21:28:49.97,
> (boolean) false]
>
> Then it issues a query like this for every row in the result set:
>
> SELECT t0.version, t1.object_id, t1.autologin_id, t1.change_summary,
> t1.changer, t1.create_date, t1.crypt_password, t1.email, t1.has_avatar,
> t1.is_admin, t1.is_editor, t1.is_moderator, t1.is_okay_to_post,
> t1.is_okay_to_submit, t1.is_owner, t1.location, t1.password_answer,
> t1.password_question, t1.premium_until, t1.uid, t1.username, t1.verified,
> t1.version, t0.alias, t0.change_summary, t0.changer, t0.create_date,
> t0.fuzzy_md5_1, t0.fuzzy_md5_2, t0.ip, t0.is_closed, t0.is_registered,
> t0.location, t0.md5, t0.message_text, t0.parent_id, t0.pending,
> t2.object_id, t2.last_change, t2.last_post, t2.post_count,
> t2.post_count_24hr, t2.posting_permitted, t2.source_id, t2.source_type,
> t0.uid, t0.user_class
> FROM Message t0
> INNER JOIN Account t1 ON t0.account_id = t1.object_id
> LEFT OUTER JOIN message_root t2 ON t0.root_id = t2.object_id
> WHERE t0.object_id = ? [params=(long) 160497]
>
> (it also issues a similar query to fill in the Articles)
>
>
>
>
--
"Courage isn't just a matter of not being frightened, you know. It's being
afraid and doing what you have to do anyway."
Doctor Who - Planet of the Daleks
This message has been brought to you by Nick Johnson 2.3b1 and the number 6.
http://healerNick.com/ http://morons.org/ http://spatula.net/