While I do agree wholeheartedly with most of what you say, I do think it's unfair to say that the requirement is "fundamentally broken". That label should be reserved for SQL itself ;-)
If there is some opaque business rule R that operates on a large set of input data, and that rule is implemented in Java, using Hibernate to get the input data, it's quite possible that the rule generates a large list of entities to retrieve. Is there a better way to batch fetch a s***load of specific entities, given a List or Set of identifiers? Here's another scenario. Suppose I have "master" table, suppose "orders". And another table "order_line_items",which is a one-to-many, and another table "order_state_change" which is also one-to-many. And suppose also that there is a somewhat complex (computationally or I/O cost) set of conditions to filter the retrieved results. There is a huge Inefficiency with multiple one-to-many joins (and no global temp table) because you have to either: 1) execute the complex where clause once for each one-to-many 2) build in your temp segment then fetch across your network a Cartesian product of the one-to-manys 3) use an "in (id1, id2, id3...)" clause for each one-to-many However, I agree that in reality, any of #1, #2 or #3 are unfortunately MORE desirable than my global temp table solution because of the massive headache in managing such a solution. But I DO think it's actually easier on the temp segment than to not use it, in most cases. David On 12/03/2011 05:23 AM, Max Rydahl Andersen wrote: >> One technical (and probably way out of scope!) way to handle this would >> be to use a temp table, do a batch insert of the values, then change the >> " in (v1, v2, v3...)" to " in (select v from temp)". > I think I would rather hear people complain about query exceptions happening > when > they are doing something fundamentally broken with a database than seeing > them realize their test > queries working as expected and then when they go to production with enough > data to pass the 1000 elements limit > their read only queries suddenly are trashing the temp table space and their > user need to be able to have create table permissions. > > /max > > > >> >> >> On 12/01/2011 04:20 AM, Emmanuel Bernard wrote: >>> Ah good point, I haven't thought of that problem with query splitting >>> >>> On 30 nov. 2011, at 22:20, Steve Ebersole wrote: >>> >>>> Splitting is not always an option. Consider a predicate like: >>>> >>>> ... where a in (x1, ... x2000) and b in (y1, ... y2000) ... >>>> >>>> If you split this up, you will have misses. Yes, it works if you can keep >>>> it all in one query because you can structure it to maintain the original >>>> semantics. However, please read the comments on that JIRA issue. For >>>> some databases, this restriction is not just on the number of elements in >>>> a in-list, but on the number of parameters overall. Splitting these 2 >>>> in-lists about into 4 does not address that. >>>> >>>> I commented on the issue that I am actually inclined to simply reject this >>>> one. In fact, I thought we already did. Maybe that was another earlier >>>> one? >>>> >>>> >>>> On Wed 30 Nov 2011 04:45:55 AM CST, Emmanuel Bernard wrote: >>>>> Also note that there is a limit for the query size globally in some >>>>> vendors and that people relieved from HHH-1123 cal fall into the second >>>>> limit. >>>>> A solution would be for Hibernate to split one query into several but I'm >>>>> not sure I like the idea. >>>>> >>>>> Emmanuel >>>>> >>>>> On 29 nov. 2011, at 21:29, Łukasz Antoniak wrote: >>>>> >>>>>> Hi all! >>>>>> >>>>>> Recently I had a closer look at HHH-1123 issue. This bug affects both - >>>>>> Criteria API and HQL. I have introduced >>>>>> Dialect#maximumInExpressionElements() method which returns maximum >>>>>> number of allowed elements in a single SQL IN clause, or null treated as >>>>>> infinite. The change of InExpression was very easy. However, fixing this >>>>>> bug for HQL queries requires modification of ParameterMetadata >>>>>> (namedDescriptorMap cannot remain unmodifiable), as well as >>>>>> AbstractQueryImpl (queryString). As I don't see any other solution, I >>>>>> wanted to ask you guys for suggestions. Is it the only possible way of >>>>>> fixing this issue? Finally, shall we really fix this? This is a DB >>>>>> vendor limitation, but 40 user gave their vote for it. >>>>>> >>>>>> Regards, >>>>>> Lukasz Antoniak >>>>>> _______________________________________________ >>>>>> hibernate-dev mailing list >>>>>> hibernate-dev@lists.jboss.org >>>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >>>>> _______________________________________________ >>>>> hibernate-dev mailing list >>>>> hibernate-dev@lists.jboss.org >>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >>>> -- >>>> st...@hibernate.org >>>> http://hibernate.org >>> _______________________________________________ >>> hibernate-dev mailing list >>> hibernate-dev@lists.jboss.org >>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >> _______________________________________________ >> hibernate-dev mailing list >> hibernate-dev@lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/hibernate-dev > /max > http://about.me/maxandersen > > > _______________________________________________ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
