tag:blogger.com,1999:blog-6877629428951398731.post7037313274299181557..comments2024-01-09T09:02:40.935-08:00Comments on Java Persistence Performance: EclipseLink supports HQL and several advanced new JPQL featuresJameshttp://www.blogger.com/profile/07275512393744882781noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-6877629428951398731.post-47161764002260317482013-07-04T07:55:30.269-07:002013-07-04T07:55:30.269-07:00FWIW, I think I've found a hacky workaround fo...FWIW, I think I've found a hacky workaround for EclipseLink 2.4. I can rewrite a JPQL predicate like:<br /><br />x.y in :ys<br /><br />as:<br /><br />SQL('exists (select null from table(?) where column_value = ?)', :ys, x.y)<br /><br />Now I call this JPQL query by passing in a JDBC Array object as the paramter value for "ys". At least this lets us keep using JPQL.Knut Wannhedenhttps://www.blogger.com/profile/18257067188039583957noreply@blogger.comtag:blogger.com,1999:blog-6877629428951398731.post-80245724013035727732013-07-02T21:32:32.891-07:002013-07-02T21:32:32.891-07:00OK, thanks. We have successfully used this ARRAY a...OK, thanks. We have successfully used this ARRAY approach in the past with standard JDBC API. I suppose we could try this using the "EclipseLink native query API" as you suggest.<br /><br />Unfortunately we have rather many JPQL queries with this problem. As a workaround we now make sure to use fixed length IN lists (when necessary by padding with repetitions of elements) to avoid hard parses. Rewriting all these queries to native SQL is not really an option for us. Another option which also works is to have a "technical entity" containing the IN list values which gets mapped to a temporary table in the database and then joining this entity in the JPQL queries.Knut Wannhedenhttps://www.blogger.com/profile/18257067188039583957noreply@blogger.comtag:blogger.com,1999:blog-6877629428951398731.post-2534137919511265472013-07-02T06:06:33.869-07:002013-07-02T06:06:33.869-07:00I would start just trying to execute it as a nativ...I would start just trying to execute it as a native query, binding the VARRAY as the parameter.<br />Jameshttps://www.blogger.com/profile/07275512393744882781noreply@blogger.comtag:blogger.com,1999:blog-6877629428951398731.post-31806451210530070572013-06-28T02:06:11.223-07:002013-06-28T02:06:11.223-07:00Yes, this is almost exactly the pattern we would l...Yes, this is almost exactly the pattern we would like to use instead of the IN list with literals. In some cases it may possibly be necessary to throw in a CAST and to make it easier to translate (no additional tables to join in the main query) it should also be possible to formulate the predicate as an IN or EXISTS predicate with the VARRAY TABLE inside a subquery.<br /><br />I would be interested to know how I could extend EclipseLink to do this. Any pointers?Knut Wannhedenhttps://www.blogger.com/profile/18257067188039583957noreply@blogger.comtag:blogger.com,1999:blog-6877629428951398731.post-87679025925192668272013-06-27T06:18:37.056-07:002013-06-27T06:18:37.056-07:00I found a good thread on this issue here,
http://...I found a good thread on this issue here,<br /><br />http://stackoverflow.com/questions/6956025/use-oracle-unnested-varrays-instead-of-in-operator<br /><br />Sounds like it would be worth some investigation, may make a good blog post.Jameshttps://www.blogger.com/profile/07275512393744882781noreply@blogger.comtag:blogger.com,1999:blog-6877629428951398731.post-34843689031718896702013-06-25T06:43:15.893-07:002013-06-25T06:43:15.893-07:00James,
No, it isn't for composite IDs. The da...James,<br /><br />No, it isn't for composite IDs. The data in the IN lists comes from an external system and the query roughly looks like this:<br /><br /> select distinct pv_sib<br /> from cu<br /> join cu.included pv<br /> join cu.required pv_sib<br /> where pv.uuid in :pvs<br /> and cu.archived = false<br /> and cu.latest = true<br /> and pv_sib.release.uuid in :releases<br /> and cu.uuid not in :ignoredCUs<br /> and not exists (select pv_inst<br /> from ci<br /> join ci.pvs pv_inst<br /> where ci.stage.version = :stage<br /> and pv_inst = pv_sib<br /> )<br /><br />Meanwhile I think I understand that array lists won't help here (I was mislead by the name to believe that it translated to JDBC array bind variables). I assume the best current alternative is to use a temporary table (maybe even mapped by a "technical" JPA entity) to hold the elements. But I would still be interested in your thoughts on how to differently translate IN list predicates to SQL (see https://bugs.eclipse.org/bugs/show_bug.cgi?id=392762), as mapping directly to SQL IN lists can clearly cause problems (a hard parse for every query with a different number of elements in the list(s)).Knut Wannhedenhttps://www.blogger.com/profile/18257067188039583957noreply@blogger.comtag:blogger.com,1999:blog-6877629428951398731.post-43824226651658933472013-06-24T07:16:39.861-07:002013-06-24T07:16:39.861-07:00@knut - What is your query with a 40s parse? Are ...@knut - What is your query with a 40s parse? Are you generating your own AND/OR for a huge list of composite ids? Where did this ids come from? I assume from some original query, perhaps instead join that original query.Jameshttps://www.blogger.com/profile/07275512393744882781noreply@blogger.comtag:blogger.com,1999:blog-6877629428951398731.post-88709461866042545062013-06-20T09:48:52.751-07:002013-06-20T09:48:52.751-07:00Hi James
Would array expressions also work as an ...Hi James<br /><br />Would array expressions also work as an alternative to normal IN-lists? I.e. could this be an alternative solution to the problem in bug 392762? If so, that would be great news. We currently have a rather simple JPQL query that due to two long IN-lists results in an SQL query that on Oracle 11.2 requires a parse time of 40 seconds!<br /><br />Thanks,<br /><br />--knutKnut Wannhedenhttps://www.blogger.com/profile/18257067188039583957noreply@blogger.com