Jan, thank you for the suggestion. I gave that some consideration but 1) the data for the two AND conditions is input values and I can't use subquery or outer-joins to mimic it and 2) the app has so many AND condition with list (IN/HAVING) that it seems overwhelming to change them all; I may wait for a fix.
Thomas I would appreciate the opportunity to help any way I can to improve this (except I'm afraid monetarily); is it an issue you are aware of and if so do you have a plan or test build I could try, or other way (test cases, code todo etc) I could help? Thanks, Ken On Feb 23, 12:41 pm, Jan Kotek <[email protected]> wrote: > Hi, > I had simular problem. H2 does not optimize AND conditions very well. > > I would recommend using subqueries. > > Regards, > j > > On Tue, Feb 23, 2010 at 5:06 PM, kensystem <[email protected]> wrote: > > Let me apologize about the formatting, I thought that G Groups would > > have retained the HTML-table formatting I coped pasted from the info > > schema (it certainly allowed in in the textarea anyway) > > CREATE table OM (pk BIGINT primary key auto_increment, b INTEGER) > > CREATE INDEX b ON om(b) > > > Also I typod '500,00' but meant the table has '500,000' rows. > > > Also to be clearer, the 'pk' column is a primary key and so only about > > 40 items in the set matched the query; even so adding the second AND- > > IN seems to slow it down exponentially as that list grows; its as if > > the entire dataset and not subset is searched for each value in each > > list. > > > -- > > You received this message because you are subscribed to the Google Groups > > "H2 Database" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]. > > For more options, visit this group > > athttp://groups.google.com/group/h2-database?hl=en. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
