Forgot the link http://base.thep.lu.se/ticket/1232
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