Hi Nicklas,
I agree that the query is rather complex and has to take some time to
execute, but over 200ms? Besides, the effect that it is causing
(extremely long experiment overview loading) is really annoying.
I had a look at db server logs and found the query:
select distinct annotation0_."id" as id1_0_0_,
annotation6_."id" as id1_6_1_,
annotation0_."version" as version2_0_0_,
... fields from annotation0_ and annotation6_ tables...
annotation6_."projectkey_id" as projectkey19_6_1_,
annotation6_."owner" as owner20_6_1_
from "Annotations" annotation0_
left outer join "InheritedAnnotations" inheriting1_ on
annotation0_."id"=inheriting1_."annotation_id"
left outer join "AnnotationSets" annotation2_ on
inheriting1_."annotationset_id"=annotation2_."id"
left outer join "AnnotationSets" annotation3_ on
annotation0_."annotationset_id"=annotation3_."id"
left outer join "InheritedAnnotationSets" inheriting4_ on
annotation3_."id"=inheriting4_."inherited_id"
left outer join "AnnotationSets" annotation5_ on
inheriting4_."annotationset_id"=annotation5_."id"
inner join "AnnotationTypes" annotation6_ on
annotation0_."annotationtype_id"=annotation6_."id"
where annotation2_."id"=$1 or annotation5_."id"=$1 order by
annotation6_."name" ASC
It contains 3 joins with no reason (adding AnnotationSets table 3 times,
never using fields exclusively belonging to it). When I removed the 3
joins (and fixed dependencies) the FROM-WHERE part looks like:
from "Annotations" annotation0_
left outer join "InheritedAnnotations" inheriting1_ on
annotation0_."id"=inheriting1_."annotation_id"
left outer join "InheritedAnnotationSets" inheriting4_ on
annotation0_."annotationset_id"=inheriting4_."inherited_id"
inner join "AnnotationTypes" annotation6_ on
annotation0_."annotationtype_id"=annotation6_."id"
where inheriting1_."annotationset_id"=$1 or
inheriting4_."annotationset_id"=$1 order by annotation6_."name" ASC
New query executes in ~5ms, so much faster. I am not sure how easy it is
to apply the fix on the java-code level, but I guess it should be possible.
Another way for quick and easy fix is to perform two queries and merge
results: one query for directly inherited annotations (WHERE
inheriting1_."annotationset_id"=$1) and the other for indirectly
inherited (WHERE inheriting4_."annotationset_id"=$1).
Each of these queries runs ~2.5ms and merging to lists in java shouldn't
take too long either. The only problem I see could be duplicate elements
on the merged list, but as long as annotation can't be inherited
directly and indirectly by the same annotation set, the fix should work.
Will you consider applying the fix to one of the next releases?
Pawel
Nicklas Nordborg wrote:
> Pawel Sztromwasser wrote:
>> Hello,
>>
>> We have noticed that Experiment overview is very slow for large
>> experiments (>40 raw bioassays) with many items annotated (mainly
>> samples). I takes over a minute for some of the experiments to load
>> overview (or re-validate). I narrowed the issue down to this line:
>>
>> List<Annotation> inherited = annotationQuery.list(dc);
>>
>> in addAnnotations(..) method (ExperimentOverview.java).
>>
>> The list(dc) method takes ~200ms to complete. It is not much, but
>> multiplied by number of items on the way from rawbioassay down to
>> biosource (annotations are checked for each of them) and by number of
>> rawbioassays... it causes most of the delay.
>>
>> Does anybody see similar problem? It is probably just a non-optimal
>> postgres query, if experiment overview works fine on mysql dbs.
>
> Annotations are rather expensive in terms of database access. Each value
> usually needs 2 separate queries to retrieve. In other words, it is not
> unusual that it takes a lot of time.
>
> /Nicklas
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by:
> SourcForge Community
> SourceForge wants to tell your story.
> http://p.sf.net/sfu/sf-spreadtheword
> _______________________________________________
> The BASE general discussion mailing list
> basedb-users@lists.sourceforge.net
> unsubscribe: send a mail with subject "unsubscribe" to
> basedb-users-requ...@lists.sourceforge.net
------------------------------------------------------------------------------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
The BASE general discussion mailing list
basedb-users@lists.sourceforge.net
unsubscribe: send a mail with subject "unsubscribe" to
basedb-users-requ...@lists.sourceforge.net
