Re: [Treebase-devel] Server sluggishness

[William P. <wil...@ya...>]

On Jun 18, 2010, at 11:49 AM, Jon Auman wrote:

> I propose 10 minutes

Or, might it make sense to match it to the proxy time-out? (is that 20 min?). Would a PostgreSQL-driven time-out trigger a rollback?

I find the query a bit odd. The parts in bold don't make much sense to me. It seems to be asking for records related to a particular column of a particular matrix. From that column, it asks for the PHYLOCHAR (i.e. the title for a character, e.g. "Feather color") and then it asks for MATRIXDATATYPE of the related matrix -- i.e. type of matrix (e.g. "morphological"), but then I think it asks for all PHYLOCHARs that also have the same type of matrix. This would create a very big selection (e.g. all the titles for all the characters that belong to matrices of type morphological), and I can't imagine why we'd want this.  Likewise, after finding the related MATRIX record, it later asks for all MATRIX records that belong to the CHARSET that belonged to the original MATRIX record.  

So two "loops" that revisit the same tables, but arriving at different directions and would seem to create non-sensical selections.

Is there a way to identify which method/class/etc in the code has called this long query?

bp



select *
from MATRIXCOLUMN matrixcolu0_ 
left outer join PHYLOCHAR phylochar1_ on matrixcolu0_.PHYLOCHAR_ID=phylochar1_.PHYLOCHAR_ID 
left outer join MATRIX characterm2_ on matrixcolu0_.MATRIX_ID=characterm2_.MATRIX_ID 
left outer join MATRIXDATATYPE matrixdata3_ on characterm2_.MATRIXDATATYPE_ID=matrixdata3_.MATRIXDATATYPE_ID 
left outer join PHYLOCHAR phylochar4_ on matrixdata3_.PHYLOCHAR_ID=phylochar4_.PHYLOCHAR_ID 
left outer join ANCSTATESET ancstatese5_ on characterm2_.ANCSTATESET_ID=ancstatese5_.ANCSTATESET_ID 
left outer join CodonPositionSet codonposit6_ on characterm2_.CODONPOSITIONSET_ID=codonposit6_.CodonPositionSet_ID 
left outer join CHARSET excludedch7_ on characterm2_.CHARSET_ID=excludedch7_.CHARSET_ID 
left outer join MATRIX characterm8_ on excludedch7_.MATRIX_ID=characterm8_.MATRIX_ID 
left outer join TYPESET typeset9_ on characterm2_.TYPESET_ID=typeset9_.TYPESET_ID 
left outer join CHARWEIGHTSET charweight10_ on characterm2_.CHARWEIGHTSET_ID=charweight10_.CHARWEIGHTSET_ID 
where
matrixcolu0_.MATRIX_ID=$1 and matrixcolu0_.COLUMN_ORDER=$2