Done
Nicklas Nordborg wrote:
> 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
------------------------------------------------------------------------------
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
