Yes, this has been an issue since Hypersonic days. If you look at the Patch Tracker you will even find a patch for version 1.61 to speed up a class of such queries. IMO, this patch was a bit complicated and I promised to try find a a more straightforward solution.
The solution involves revamping the classes that perform a SLECT, including TableFilter.java, to make better use of the indexes. This was included in the list of things to do for 1.7.2, but so far I have not had the time to work on it. The workaround for this sort of problem can sometimes be a combination of set queries such as UNION, INTERSECT, EXCEPT, and sometimes TEMP tables. The idea is to break the conditions down to simple ones that can be processed very quickly and either use the set queries to return a combined result, or use a temp table to store interim results and run the next query against it. I hope this limitation will prove to be temporary one and we can improve the query engine for the next version. Fred Toussi __________________ Alex Kempkens wrote: Hey, that's a little bit a performance topic. What do you think is the most effective way to select much data. I have a JSP based application and normally there is no problem with a sequencial data select. But I was thinking about if it might help to search in huge tables or with complex queries if I use more than one connection at one time. Also possibly use different threads for quering all the information I need. The background is. I've a complex structure with a n:m relationship between two tables. And now I have a query that should have a result with all the records from the master table for a given record of the second table. But not only one there can be many of these second creteria connect with and/or operators. Master (ID, Name) // the result I want Criterion (ID, ...) // The possible creteria i want to search for Master2Criterion (ID_Master, ID_Criterion) If I look for one its easy. select Master.ID, Master.Name from Master, Master2Criterion where Master.ID = Master2Criterion.ID_Master and Master2Criterion.ID_Criterion = searchValue but if you want to search for more than one ... an other and Master2Criterion.ID_Criterion = searchValue2 can of course not work. Any solution I found as a select is very slow. So I have to look for something to help hsqldb. Thanks for help Alex ------------------------------------------------------- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf _______________________________________________ hsqldb-developers mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
