Hsqldb 2.2.8: select in clause inefficiency

  • Lucille Wilson

    Lucille Wilson - 2012-10-22

    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.

  • Fred Toussi

    Fred Toussi - 2012-10-22

    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.

  • Lucille Wilson

    Lucille Wilson - 2012-10-23

    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.