> 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 ------------------------------------------------------------------------------ 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]
