Jari Häkkinen writes: > 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.
Just to remind you about http://base.thep.lu.se/ticket/903 which highlights a payoff between fast bioassayset creation and slow experiment explorer browsing. But I see that Jari has already commented on the ticket a few months ago so is probably aware of it. regards, Bob. > > > 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 > >> 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 -- Bob MacCallum | VectorBase Developer | Kafatos/Christophides Groups | Division of Cell and Molecular Biology | Imperial College London | Phone +442075941945 | Email r.maccal...@imperial.ac.uk ------------------------------------------------------------------------------ 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
