openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nick Johnson <>
Subject how to avoid large number of queries?
Date Thu, 15 Nov 2007 05:46:17 GMT
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 

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.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.

View raw message