openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From WPrecht <>
Subject Re: Subqueries in JPQL
Date Wed, 08 Oct 2008 00:06:51 GMT

Judes Tumuhairwe wrote:
> Also, you might consider if you really want to put a query like that in
> the
> application. Given the performace penalty (depending on your number of
> users, concurrency, etc.), you might want to put it in the database as a
> stored procedure or something. Just something to think about.
> Having said that; to be able to construct that query in to JPQL
> (considering
> that SQL is set-based and JPQL is object-based), I would suggest
> deconstructing/"reverse-engineering" it by writing it out what it should
> do
> (the requirements so to say) (with emphasis on objects & their actions
> [LastEdit, Conferences, Unread, etc.]). and model those objects & go from
> there.

I have been given an application to tune by a PM who's basically against
stored procedures and sweating a deadline, so I don't have the latitude to
make major code changes.  Currently, one action in the app makes 2n+1 trips
to the database to build up a list of data.  One to get the list of
conferences, one per conference to get the total note count and one per to
get the unread note count.  Needless to say, that makes the DBA's head spin.

All three of those queries are fairly straightforward JPQL already and work
fine, unless the dataset starts to get large.  I would like to get them all
into one query; I know it's really the same number of selects on the data,
but at least there is less overhead.

For instance the outer query looks like:

SELECT conf FROM Conference conf 
WHERE conf.sectionId = ?1 AND conf.deleted <> 1
ORDER BY conf.displaySeq

One of the inners looks like:

SELECT COUNT(cn) FROM ConferenceNotes cn
WHERE cn.confId = ?1 AND cn.DELETED <> 1

The other is pretty similar.  That Last_Edit table is joined to both the
Conference entity and the ConferenceNote which makes the JPQL syntax a lot

Thanks for the input guys!  I might play with this some more and see if I
can restructure it and come at it from another angle.

View this message in context:
Sent from the OpenJPA Users mailing list archive at

View raw message