Long feature residues in Chado

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Long feature residues in Chado

Becksfort, Jared

Hello,

 

I am using Chado from GMOD-1.1 on a PostgreSQL 8.4.3 server.

 

I have noticed that if I “select * from feature…” the queries take a tremendously long time, timing out most of the client apps.  I am pretty sure that this is due to the length of the text field ‘residues’ on some of our longer features such as chromosomes.  If I “select feature_id, name from feature…” or select features that are not chromosomes, the results come back very quickly.  So whenever I query the feature table, I need to specify all the columns except residues.

 

I was wondering if it would make sense to have a separate feature_residues table, or even better, if there is an quick and obvious solution to this problem that I have not thought about.

 

Thanks,

Jared



Email Disclaimer: www.stjude.org/emaildisclaimer

------------------------------------------------------------------------------
Start uncovering the many advantages of virtual appliances
and start using them to simplify application deployment and
accelerate your shift to cloud computing.
http://p.sf.net/sfu/novell-sfdev2dev
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
Reply | Threaded
Open this post in threaded view
|

Re: Long feature residues in Chado

Chris Mungall

How about some views that decompose the feature relation into two  
relations, e.g. feature_info and feature_residues? There may be some  
views similar to this as part of the standard schema def already.

On Sep 28, 2010, at 2:54 PM, Becksfort, Jared wrote:

> Hello,
>
> I am using Chado from GMOD-1.1 on a PostgreSQL 8.4.3 server.
>
> I have noticed that if I “select * from feature…” the queries take a  
> tremendously long time, timing out most of the client apps.  I am  
> pretty sure that this is due to the length of the text field  
> ‘residues’ on some of our longer features such as chromosomes.  If I  
> “select feature_id, name from feature…” or select features that are  
> not chromosomes, the results come back very quickly.  So whenever I  
> query the feature table, I need to specify all the columns except  
> residues.
>
> I was wondering if it would make sense to have a separate  
> feature_residues table, or even better, if there is an quick and  
> obvious solution to this problem that I have not thought about.
>
> Thanks,
> Jared
>
> Email Disclaimer: www.stjude.org/emaildisclaimer
> ------------------------------------------------------------------------------
> Start uncovering the many advantages of virtual appliances
> and start using them to simplify application deployment and
> accelerate your shift to cloud computing.
> http://p.sf.net/sfu/novell-sfdev2dev_______________________________________________
> Gmod-schema mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gmod-schema


------------------------------------------------------------------------------
Start uncovering the many advantages of virtual appliances
and start using them to simplify application deployment and
accelerate your shift to cloud computing.
http://p.sf.net/sfu/novell-sfdev2dev
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
Reply | Threaded
Open this post in threaded view
|

Re: Long feature residues in Chado

Becksfort, Jared
That is a good idea, and I should probably do that for my scripts' selecting.  I think that I would still need to modify the insert scripts and other GMOD scripts and programs (Apollo) to not select * from features though.  

Part of my motivation for sending this to the list is to see if it something that other people have had problems with.  If so, then maybe the next version of the schema, insertion scripts, and Apollo could have the separate residues table.  If not, then I will just see about moving my installation to a less busy server.

Thanks,
Jared

-----Original Message-----
From: Chris Mungall [mailto:[hidden email]]
Sent: Tuesday, September 28, 2010 4:59 PM
To: Becksfort, Jared
Cc: '[hidden email]'
Subject: Re: [Gmod-schema] Long feature residues in Chado


How about some views that decompose the feature relation into two  
relations, e.g. feature_info and feature_residues? There may be some  
views similar to this as part of the standard schema def already.

On Sep 28, 2010, at 2:54 PM, Becksfort, Jared wrote:

> Hello,
>
> I am using Chado from GMOD-1.1 on a PostgreSQL 8.4.3 server.
>
> I have noticed that if I "select * from feature..." the queries take a  
> tremendously long time, timing out most of the client apps.  I am  
> pretty sure that this is due to the length of the text field  
> 'residues' on some of our longer features such as chromosomes.  If I  
> "select feature_id, name from feature..." or select features that are  
> not chromosomes, the results come back very quickly.  So whenever I  
> query the feature table, I need to specify all the columns except  
> residues.
>
> I was wondering if it would make sense to have a separate  
> feature_residues table, or even better, if there is an quick and  
> obvious solution to this problem that I have not thought about.
>
> Thanks,
> Jared
>
> Email Disclaimer: www.stjude.org/emaildisclaimer
> ------------------------------------------------------------------------------
> Start uncovering the many advantages of virtual appliances
> and start using them to simplify application deployment and
> accelerate your shift to cloud computing.
> http://p.sf.net/sfu/novell-sfdev2dev_______________________________________________
> Gmod-schema mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gmod-schema




------------------------------------------------------------------------------
Start uncovering the many advantages of virtual appliances
and start using them to simplify application deployment and
accelerate your shift to cloud computing.
http://p.sf.net/sfu/novell-sfdev2dev
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
Reply | Threaded
Open this post in threaded view
|

Re: Long feature residues in Chado

Scott Cain
Hi Jared,

I don't think GBrowse, Apollo or the loading scripts ever do a select
*.  Generally, it is a bad idea to do in application code for a
variety of reasons.  (Of course, now that I've written that, somebody
will no doubt point out somewhere I've done it. :-)

Scott


On Tue, Sep 28, 2010 at 6:12 PM, Becksfort, Jared
<[hidden email]> wrote:

> That is a good idea, and I should probably do that for my scripts' selecting.  I think that I would still need to modify the insert scripts and other GMOD scripts and programs (Apollo) to not select * from features though.
>
> Part of my motivation for sending this to the list is to see if it something that other people have had problems with.  If so, then maybe the next version of the schema, insertion scripts, and Apollo could have the separate residues table.  If not, then I will just see about moving my installation to a less busy server.
>
> Thanks,
> Jared
>
> -----Original Message-----
> From: Chris Mungall [mailto:[hidden email]]
> Sent: Tuesday, September 28, 2010 4:59 PM
> To: Becksfort, Jared
> Cc: '[hidden email]'
> Subject: Re: [Gmod-schema] Long feature residues in Chado
>
>
> How about some views that decompose the feature relation into two
> relations, e.g. feature_info and feature_residues? There may be some
> views similar to this as part of the standard schema def already.
>
> On Sep 28, 2010, at 2:54 PM, Becksfort, Jared wrote:
>
>> Hello,
>>
>> I am using Chado from GMOD-1.1 on a PostgreSQL 8.4.3 server.
>>
>> I have noticed that if I "select * from feature..." the queries take a
>> tremendously long time, timing out most of the client apps.  I am
>> pretty sure that this is due to the length of the text field
>> 'residues' on some of our longer features such as chromosomes.  If I
>> "select feature_id, name from feature..." or select features that are
>> not chromosomes, the results come back very quickly.  So whenever I
>> query the feature table, I need to specify all the columns except
>> residues.
>>
>> I was wondering if it would make sense to have a separate
>> feature_residues table, or even better, if there is an quick and
>> obvious solution to this problem that I have not thought about.
>>
>> Thanks,
>> Jared
>>
>> Email Disclaimer: www.stjude.org/emaildisclaimer
>> ------------------------------------------------------------------------------
>> Start uncovering the many advantages of virtual appliances
>> and start using them to simplify application deployment and
>> accelerate your shift to cloud computing.
>> http://p.sf.net/sfu/novell-sfdev2dev_______________________________________________
>> Gmod-schema mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/gmod-schema
>
>
>
>
> ------------------------------------------------------------------------------
> Start uncovering the many advantages of virtual appliances
> and start using them to simplify application deployment and
> accelerate your shift to cloud computing.
> http://p.sf.net/sfu/novell-sfdev2dev
> _______________________________________________
> Gmod-schema mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gmod-schema
>



--
------------------------------------------------------------------------
Scott Cain, Ph. D.                                   scott at scottcain dot net
GMOD Coordinator (http://gmod.org/)                     216-392-3087
Ontario Institute for Cancer Research

------------------------------------------------------------------------------
Start uncovering the many advantages of virtual appliances
and start using them to simplify application deployment and
accelerate your shift to cloud computing.
http://p.sf.net/sfu/novell-sfdev2dev
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
Reply | Threaded
Open this post in threaded view
|

Re: Long feature residues in Chado

Becksfort, Jared
OK, that is good to know.  Thanks Chris and Scott for your help.

Jared

-----Original Message-----
From: Scott Cain [mailto:[hidden email]]
Sent: Tuesday, September 28, 2010 6:11 PM
To: Becksfort, Jared
Cc: Chris Mungall; [hidden email]
Subject: Re: [Gmod-schema] Long feature residues in Chado

Hi Jared,

I don't think GBrowse, Apollo or the loading scripts ever do a select
*.  Generally, it is a bad idea to do in application code for a
variety of reasons.  (Of course, now that I've written that, somebody
will no doubt point out somewhere I've done it. :-)

Scott


On Tue, Sep 28, 2010 at 6:12 PM, Becksfort, Jared
<[hidden email]> wrote:

> That is a good idea, and I should probably do that for my scripts' selecting.  I think that I would still need to modify the insert scripts and other GMOD scripts and programs (Apollo) to not select * from features though.
>
> Part of my motivation for sending this to the list is to see if it something that other people have had problems with.  If so, then maybe the next version of the schema, insertion scripts, and Apollo could have the separate residues table.  If not, then I will just see about moving my installation to a less busy server.
>
> Thanks,
> Jared
>
> -----Original Message-----
> From: Chris Mungall [mailto:[hidden email]]
> Sent: Tuesday, September 28, 2010 4:59 PM
> To: Becksfort, Jared
> Cc: '[hidden email]'
> Subject: Re: [Gmod-schema] Long feature residues in Chado
>
>
> How about some views that decompose the feature relation into two
> relations, e.g. feature_info and feature_residues? There may be some
> views similar to this as part of the standard schema def already.
>
> On Sep 28, 2010, at 2:54 PM, Becksfort, Jared wrote:
>
>> Hello,
>>
>> I am using Chado from GMOD-1.1 on a PostgreSQL 8.4.3 server.
>>
>> I have noticed that if I "select * from feature..." the queries take a
>> tremendously long time, timing out most of the client apps.  I am
>> pretty sure that this is due to the length of the text field
>> 'residues' on some of our longer features such as chromosomes.  If I
>> "select feature_id, name from feature..." or select features that are
>> not chromosomes, the results come back very quickly.  So whenever I
>> query the feature table, I need to specify all the columns except
>> residues.
>>
>> I was wondering if it would make sense to have a separate
>> feature_residues table, or even better, if there is an quick and
>> obvious solution to this problem that I have not thought about.
>>
>> Thanks,
>> Jared
>>
>> Email Disclaimer: www.stjude.org/emaildisclaimer
>> ------------------------------------------------------------------------------
>> Start uncovering the many advantages of virtual appliances
>> and start using them to simplify application deployment and
>> accelerate your shift to cloud computing.
>> http://p.sf.net/sfu/novell-sfdev2dev_______________________________________________
>> Gmod-schema mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/gmod-schema
>
>
>
>
> ------------------------------------------------------------------------------
> Start uncovering the many advantages of virtual appliances
> and start using them to simplify application deployment and
> accelerate your shift to cloud computing.
> http://p.sf.net/sfu/novell-sfdev2dev
> _______________________________________________
> Gmod-schema mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gmod-schema
>



--
------------------------------------------------------------------------
Scott Cain, Ph. D.                                   scott at scottcain dot net
GMOD Coordinator (http://gmod.org/)                     216-392-3087
Ontario Institute for Cancer Research



------------------------------------------------------------------------------
Start uncovering the many advantages of virtual appliances
and start using them to simplify application deployment and
accelerate your shift to cloud computing.
http://p.sf.net/sfu/novell-sfdev2dev
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema