Hi all, The 2.11 release will target performance issues. The next release is 2.10 but we have not set a dead line for the 2.10 release yet. However, a hopefully performance boosted BASE 2.11 should be out during the spring, until then we have to live with some slow BASE clicks. I agree with Pawel that the Experiment overview is way too slow. The BASE team is aware of several performance issues but we have so far not prioritized them.
Please contribute with performances issues to be examined and also ideas on how to resolve them. Jari - Change we believe in ... an even better BASE. Pawel Sztromwasser 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? > > Pawel > > > Nicklas Nordborg wrote: >> Pawel Sztromwasser wrote: >>> Hello, >>> >>> We have noticed that Experiment overview is very slow for large >>> experiments (>40 raw bioassays) with many items annotated (mainly >>> samples). I takes over a minute for some of the experiments to load >>> overview (or re-validate). I narrowed the issue down to this line: >>> >>> List<Annotation> inherited = annotationQuery.list(dc); >>> >>> in addAnnotations(..) method (ExperimentOverview.java). >>> >>> The list(dc) method takes ~200ms to complete. It is not much, but >>> multiplied by number of items on the way from rawbioassay down to >>> biosource (annotations are checked for each of them) and by number of >>> rawbioassays... it causes most of the delay. >>> >>> Does anybody see similar problem? It is probably just a non-optimal >>> postgres query, if experiment overview works fine on mysql dbs. >> Annotations are rather expensive in terms of database access. Each value >> usually needs 2 separate queries to retrieve. In other words, it is not >> unusual that it takes a lot of time. >> >> /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] > > > ------------------------------------------------------------------------------ > 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]
