Re: [Hsqldb-user] Performance of UNION

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

Ok, that's nice, thanks.
Is it even possible to access a 1.8 database using the new 1.9 jar so 
that no migration is needed?
Do you know when Hibernate support for HSQLDB 1.9 is adapted? I mean the 
dialect class.

Regards,
Christian

fredt schrieb:
> The DatabaseMetaData interface has methods for driver / database version.
>
> Migrating user data from 1.8 to 1.9 should be transparent.
>
> Fred
> ----- Original Message ----- 
> From: "Christian Beil" <chr...@we...>
> To: "HSQLdb user discussions" <hsq...@li...>
> Sent: 24 June 2009 15:05
> Subject: Re: [Hsqldb-user] Performance of UNION
>
>
> Hi Fred,
>
> This is great.
> I usually use cached tables.
> So, if startup and shutdown time has dramatically improved in 1.9, I
> will migrate my application.
> Is there a way to figure out over the JDBC connection which version of
> HSQLDB I am connected to?
> Since I need to migrate user's data to the newer version of the HSQLDB
> format.
>
> Regards,
> Christian
>
>
>
> fredt schrieb:
>   
>> I don't know about the possible speed of processing the data outside
>> queries, as this depends on the contents of the rows.
>>
>> Regarding loading and shutdown speed, 1.9 has very fast shutdown and 
>> startup
>> with cached tables.
>>
>> Later, we may also improve this for memory tables.
>>
>> Fred
>> ----- Original Message ----- 
>> From: "Christian Beil" <chr...@we...>
>> To: "HSQLdb user discussions" <hsq...@li...>
>> Sent: 23 June 2009 23:05
>> Subject: Re: [Hsqldb-user] Performance of UNION
>>
>>
>> Hi Fred,
>>
>> Thanks for your answer.
>> You're right, of course, I was mistaken.
>> The two partial queries sum up to about 10 seconds.
>> What takes so long are the NOT EXISTS subqueries, of course.
>> The HITMAPID has an index and reduces the rows to a few hundred in some
>> cases, but not always.
>> Unfortunately, I see no way to rewrite the queries without the NOT
>> EXISTS subselect.
>> The query is generated dynamically and in some cases this subselect is
>> not included, which makes it a lot faster.
>> Perhaps I should leave the subselects out and do the additional
>> filtering in memory when I need to?
>> Thanks for your help.
>>
>> I have another question regarding performance.
>> HSQLDB seems to get slower if there is a lot of data in it.
>> We have some cases where up to several million records are stored in the
>> database.
>> Is there a way to improve loading and shutdown times?
>> Thanks again.
>>
>> Cheers,
>> Christian
>>
>>
>>
>> fredt schrieb:
>>
>>     
>>> The UNION is performed after a result set is created for each of the
>>> SELECT
>>> statements. You can verify that a UNION of 100 rows from two simple
>>> selects
>>> takes a few milliseconds extra. Therefore your time mesurement does not
>>> look
>>> correct.
>>>
>>> The SELECT statements will take a long time unless "HITS"."HITMAPID"=1
>>> reduces the rows to a few hundred and the column is indexed. You can
>>> execute
>>> EXPLAIN PLAN to find out.
>>>
>>> The NOT EXISTS subquery is a performance problem, because it is performed
>>> for each row that filters through the condition I mentioned above.
>>>
>>> Try to write the query a different way, using joins if possible.
>>>
>>> Fre
>>>
>>>
>>> ----- Original Message ----- 
>>> From: "Christian Beil" <chr...@we...>
>>> To: <hsq...@li...>
>>> Sent: 22 June 2009 18:17
>>> Subject: [Hsqldb-user] Performance of UNION
>>>
>>>
>>> Hi all,
>>>
>>> We have some performance issues with several rather complex queries that
>>> include subselects and unions.
>>> We have been evaluating the new 1.9beta for improvements in this area,
>>> but the results where about the same.
>>> Here is the query, perhaps someone has an idea and can help.
>>> I would like to know why the UNION takes so long.
>>>
>>> (
>>> select "HITS"."RECORDKEY" as "recordkey"
>>> from "HITS"
>>> left join "DATA1" on "HITS"."RECORDKEY"="DATA1"."INTELLIID"
>>> where "HITS"."HITMAPID"=1
>>> and not exists  (
>>>     select "HIT"."STOREID"
>>>     from "HIT"
>>>     where "HIT"."HITLISTS_ID"="HITS"."ID"
>>>     and "HIT"."QUALI"=0
>>>     and "HIT"."AUTO"=FALSE
>>> ))
>>> union
>>> (
>>> select "HITS"."RECORDKEY" as "recordkey"
>>> from "HITS"
>>> left join "HIT" on "HIT"."HITLISTS_ID"="HITS"."ID"
>>> left join "DATA1" on "HIT"."RECORDID"="DATA1"."INTELLIID"
>>> where "HITS"."HITMAPID"=1
>>> and not exists  (
>>>     select "HIT"."STOREID"
>>>     from "HIT"
>>>     where "HIT"."HITLISTS_ID"="HITS"."ID"
>>>     and "HIT"."QUALI"=0
>>>     and "HIT"."AUTO"=FALSE
>>> ))
>>>
>>> There are indices on all relevant columns: "HIT"."HITLISTS_ID",
>>> "HITS"."ID", "HIT"."RECORDID", "DATA1"."INTELLIID", "HITS"."RECORDKEY",
>>> "HIT"."STOREID", "HITS"."HITMAPID", "HIT"."QUALI", "HIT"."AUTO".
>>>
>>> On my laptop the whole query takes 10 seconds and returns about 100 rows.
>>> What I find interesting is that each of the partial queries that were
>>> unioned take about 1 second.
>>> Why does unioning a few hundred records from two result sets take
>>> around  seconds?
>>>
>>> I could work around this by selecting the two partial results and union
>>> them in memory via a HashSet.
>>> But I wanted to report this, so perhaps it can be improved in future
>>> versions.
>>>
>>> Thanks.
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Are you an open source citizen? Join us for the Open Source Bridge
>>> conference!
>>> Portland, OR, June 17-19. Two days of sessions, one day of unconference:
>>> $250.
>>> Need another reason to go? 24-hour hacker lounge. Register today!
>>> http://ad.doubleclick.net/clk;215844324;13503038;v?http://opensourcebridge.org
>>> _______________________________________________
>>> Hsqldb-user mailing list
>>> Hsq...@li...
>>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Are you an open source citizen? Join us for the Open Source Bridge
>>> conference!
>>> Portland, OR, June 17-19. Two days of sessions, one day of unconference:
>>> $250.
>>> Need another reason to go? 24-hour hacker lounge. Register today!
>>> http://ad.doubleclick.net/clk;215844324;13503038;v?http://opensourcebridge.org
>>> _______________________________________________
>>> Hsqldb-user mailing list
>>> Hsq...@li...
>>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>>
>>>
>>>
>>>       
>> ------------------------------------------------------------------------------
>> _______________________________________________
>> Hsqldb-user mailing list
>> Hsq...@li...
>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>
>>
>> ------------------------------------------------------------------------------
>> _______________________________________________
>> Hsqldb-user mailing list
>> Hsq...@li...
>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>
>>
>>     
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Hsqldb-user mailing list
> Hsq...@li...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user 
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Hsqldb-user mailing list
> Hsq...@li...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
>   