Thanks for this! It seems to a very thorough analysis of the problem.
Can you create a ticket for this and at least link to this thread in the
mailing list archive?
/Nicklas
Pawel Sztromwasser wrote:
> Nicklas Nordborg wrote:
>>> 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?
>> No, not in the next release. We are planning to investigate performance
>> problems for the BASE 2.11. Regarding the SQL it is something that is
>> generated by Hibernate. I don't know if it is possible to change this. Is
>> it really the main query that takes a lot of time to execute? I was more
>> thinking that the slow part is the two extra queries that are needed for
>> loading each annotation value (so if there are N annotations we need 2*N+1
>> queries to load everything).
>>
>> /Nicklas
>>
>
> Yes, it seems to be the inherited-annotation-gathering query. It is run
> for every rawbioassay in experiment 9 times (for all the items linked to
> it: rba itself, scan, hyb, array batch, array slide, labeled extract,
> extract, sample and biosource). Of course if the item doesn't have any
> annotation set, the query is not executed. But still, for an experiment
> I am testing it on (ExperimentOverview object is created in
> 50-70seconds, depending on the run) the query runs 225 times (59 rbas in
> exp) which accounts for ~45seconds, so majority of the execution time.
>
> I tried the 'quick and easy fix' from my previous mail (although
> implemented in a different way, then the first idea was; pasted below).
> Splitting the getAllInheritedAnnotations query into several: first query
> for directly inherited ann and the rest for indirectly inherited (one
> per each inherited set) helps a lot. Although the number of queries
> grows (together with complexity of the code), execution time of this
> fragment drops to ~6ms, compared to ~200ms before. And experiment
> overview loads much faster...
>
>
> // query for directly inherited annotations only
> ItemQuery<Annotation> directlyInheritedAnnotationsQuery =
> initQuery(as.getInheritedAnnotations(), "at", "name");
>
> //include join with annotation types
> directlyInheritedAnnotationsQuery.join(
> Hql.innerJoin(null, "annotationType", "at", true));
>
> //run query and create result list (for now, only inherited directly)
> List<Annotation> inherited = new ArrayList<Annotation>(
> directlyInheritedAnnotationsQuery.list(dc));
>
> // get a list of all inherited sets and query each of them for
> // annotations (indirectly inherited annotations). all found ann
> // are added to inherited list
> ItemQuery<AnnotationSet> inheritedAnnotationSetsQuery =
>
> as.getInheritedAnnotationSets();
> inheritedAnnotationSetsQuery.include(
> Include.MINE, Include.IN_PROJECT, Include.SHARED, Include.OTHERS);
>
> for (AnnotationSet set : inheritedAnnotationSetsQuery.list(dc)) {
> ItemQuery<Annotation> indirectlyInheritedAnnotationsQuery =
> initQuery(set.getAnnotations(), "at", "name");
> indirectlyInheritedAnnotationsQuery.join(
> Hql.innerJoin(null, "annotationType", "at", true));
> inherited.addAll(indirectlyInheritedAnnotationsQuery.list(dc));
> }
>
> I hope that the code does exactly the same thing that the original one
> and you could make some use of it. It is not the most pretty fix, but
> since it works and is available, maybe it could be incorporated before a
> nice one will be introduced in 2.11? If not, spring is not that far in
> time after all... :)
>
> Cheers,
> Pawel
>
>
>>
>> ------------------------------------------------------------------------------
>> 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
------------------------------------------------------------------------------
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
