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
> [email protected]
> unsubscribe: send a mail with subject "unsubscribe" to
> [email protected]
------------------------------------------------------------------------------
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
[email protected]
unsubscribe: send a mail with subject "unsubscribe" to
[email protected]
