Re: [Gusdev-gusdev] performance issues in querying dots.similarity/dots.similarityspan tables

[Angel P. <an...@ma...>]

Sucheta,
It is always a good idea to include an the output of "EXPLAIN PLAN" when 
asking how to optimize queries. Usually well-placed indexes will take 
care of long running queries.

*EXPLAIN PLAN* [*SET STATEMENT_ID* = 'text']

[*INTO* [schema .] table [@ dblink]] *FOR* statement;

Output defaults to a plan table in your schema, unless you give it a 
plan table to insert into. Oracle has a script for creating this table, 
|UTLXPLAN.SQL|
Attached is a SQL script to output the results from the plan table.

Postgres must have some similar functionality.
Angel

Sucheta Tripathy wrote:

> Folks,
>
> I don't know if any of you encountered this problem, but for us, it 
> has been a major bottleneck to query the similarity and similarityspan 
> tables to get the desirable output.
>
> For example if I say a query:
>
> $sql="select  /*+CHOOSE*/   distinct ss.query_start, 
> ss.query_end,ss.is_reversed
> ,ss.reading_frame,s.subject_id, to_char(s.pvalue_mant,'FM9990.099') || 
> 'e' || TO
> _CHAR(s.pvalue_exp,'FMS999'), eaa.name, s.similarity_id  from 
> dots.similarity s
> , dots.similarityspan ss, dots.transcript t, dots.externalaasequence 
> eaa  where
> t.na_feature_id=464205 and t.gene=to_char(s.query_id) and 
> s.similarity_id=ss.sim
> ilarity_id and s.subject_id=eaa.source_id and rownum < 10 order by 
> s.similarity_
> id";
>
> It takes atleast 5 minutes. One thing to note here is here for the 
> transcript corresponding to the na_feature_id is blasted against only 
> one database, so the query is quite straight forward. But there are 
> instances where the scaffolds are blasted aganst 23 different 
> organisms. In that case, in order to obtain the value against one 
> particular database, I use much nested query going and getting the 
> value from externaldatabase and externaldatabaserelease and then using 
> it in similarity table.
>
> I was wondering if anyone has any efficient way of querying these tables.
>
> Thanks
>
> Sucheta
>
>
>
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
> _______________________________________________
> Gusdev-gusdev mailing list
> Gus...@li...
> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev



-- 

Angel Pizarro
Director, Bioinformatics Facility
Institute for Translational Medicine and Therapeutics
University of Pennsylvania
806 BRB II/III
421 Curie Blvd.
Philadelphia, PA 19104-6160

P: 215-573-3736
F: 215-573-9004