If I have a table which has 72 records in it, but my select in clause has 15,000 items in it, hsqldb doesn't do a sortedSet on the IN clause and the select statement is very slow.
SELECT * from tableX where id in (1,2,3,4,5,…. 14999, 15000)
If however, I do the sorted set on my list before I create the IN clause, the sql runs MUCH faster. I had assumed HsqlDb was doing the sorted set but doesn't seem to be the case.
I think the extra time is due to sorting the unsorted list by HSQLDB.
If there is an index on the ID record, the query is transformed into a join. How much time does it take to execute the query with sorted and unsorted list.
There is an index on the field which has the in clause. There is a join in the select statement with appropriate indexes.
The In clause was an un ordered list with repeating values. When I ran the select with the un-ordered, repeating values in the in clause the length of time was minutes to get 72 records from the table. When applied a sorted set on the in clause list, the amount of time to do the select was miniscule. I used sql, jdbc driver, no hibernate. Just straight hsqldb.
I think it would be nice if hsqldb used a sorted set in all the lists as input into an IN clause.
Log in to post a comment.