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 cleaner. 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. Wayne. -- View this message in context: http://n2.nabble.com/Subqueries-in-JPQL-tp1301743p1305240.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
