<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 14 (filtered medium)"><!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:Helvetica;
panose-1:2 11 6 4 2 2 2 2 2 4;}
@font-face
{font-family:Helvetica;
panose-1:2 11 6 4 2 2 2 2 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Balloon Text Char";
margin:0in;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";}
span.apple-converted-space
{mso-style-name:apple-converted-space;}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.BalloonTextChar
{mso-style-name:"Balloon Text Char";
mso-style-priority:99;
mso-style-link:"Balloon Text";
font-family:"Tahoma","sans-serif";}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Thanks Andy. I kind of figured the same. Every time I actually had to go look into an API PM file I saw selects.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>The API is fast though (at least for my needs) so you did a good job. <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Good perl coders are getting pretty hard to find anymore too.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> dev-bounces@ensembl.org [mailto:dev-bounces@ensembl.org] <b>On Behalf Of </b>Andrew Yates<br><b>Sent:</b> Tuesday, December 16, 2014 4:58 PM<br><b>To:</b> Ensembl developers list<br><b>Subject:</b> Re: [ensembl-dev] SQL query to retrieve gene sequence...<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Hi Ed,<o:p></o:p></p><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>We haven’t gone too far with stored procedures preferring to keep as much logic in our Perl layer as possible. The API is pretty much a custom ORM layer running select statements over DBI. When we start looking at parts of code like sequence retrieval we apply quite a bit of caching and fetching normalisation* so even though there’s a hit in bringing things across into memory we can ensure good performance on subsequent requests.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Andy<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal style='margin-bottom:12.0pt'>* we bit-shift the start/end of sequence requests when fetching so neighbouring locations don’t cause multiple requests for sequence from MySQL. If you’re really interested you can see the core sequence retrieval logic in Bio::EnsEMBL::DBSQL::SequenceAdaptor<o:p></o:p></p><div><blockquote style='margin-top:5.0pt;margin-bottom:5.0pt'><div><p class=MsoNormal>On 16 Dec 2014, at 21:42, Ed Gray <<a href="mailto:gray_ed@hotmail.com">gray_ed@hotmail.com</a>> wrote:<o:p></o:p></p></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal style='background:white'><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Plus, a SQL-expert is pretty hard to find in bioinformatics. But I do agree with Rob that the fastest systems use SQL stored procedures. They can be made to be blazingly fast, BTDT, but I hail originally from commercial software development.</span><o:p></o:p></p></div><div><p class=MsoNormal style='background:white'><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> </span><o:p></o:p></p></div><div><p class=MsoNormal style='background:white'><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Having said that, I use the ensembl api for accessing ensembl data, when in Rome, do as the Romans. But I always wondered what was all the way under the hood, just standard SQL and perl. <span class=apple-converted-space> </span></span><o:p></o:p></p></div><div><p class=MsoNormal style='background:white'><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> </span><o:p></o:p></p></div><div><p class=MsoNormal style='background:white'><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>I guess the bottom line is at the base of ensembl is it sql executes or sql selects, updates and inserts?</span><o:p></o:p></p></div><div><p class=MsoNormal style='background:white'><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> </span><o:p></o:p></p></div><div><p class=MsoNormal style='background:white'><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> </span><o:p></o:p></p></div><div><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'><div><p class=MsoNormal style='background:white'><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span class=apple-converted-space><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> </span></span><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'><a href="mailto:dev-bounces@ensembl.org">dev-bounces@ensembl.org</a> [<a href="mailto:dev-bounces@ensembl.org">mailto:dev-bounces@ensembl.org</a>]<span class=apple-converted-space> </span><b>On Behalf Of<span class=apple-converted-space> </span></b>Rob Sargent<br><b>Sent:</b><span class=apple-converted-space> </span>Tuesday, December 16, 2014 12:08 PM<br><b>To:</b><span class=apple-converted-space> </span><a href="mailto:dev@ensembl.org">dev@ensembl.org</a><br><b>Subject:</b><span class=apple-converted-space> </span>Re: [ensembl-dev] SQL query to retrieve gene sequence...</span><o:p></o:p></p></div></div></div><div><p class=MsoNormal style='background:white'> <o:p></o:p></p></div><p class=MsoNormal style='margin-bottom:12.0pt;background:white'><span style='font-size:10.0pt;font-family:"Courier New"'>I don't know which server you are using but I had these do amazing things, and amazingly fast. Try it, you might like it. :)<br><br>rjs</span><o:p></o:p></p><div><div><p class=MsoNormal style='background:white'>On 12/16/2014 09:58 AM, Andrew Yates wrote:<o:p></o:p></p></div></div><blockquote style='margin-top:5.0pt;margin-bottom:5.0pt'><div><p class=MsoNormal style='background:white'>Hi Rob,<span class=apple-converted-space> </span><o:p></o:p></p></div><div><div><p class=MsoNormal style='background:white'> <o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'>I won’t ever say it cannot be done just in the database. More I don’t think it’ll be as performant or as easy as the alternatives myself & Kieron suggested :).<o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'> <o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'>Andy<o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'> <o:p></o:p></p></div><div><div><p class=MsoNormal style='background:white'>------------<br>Andrew Yates - Ensembl Support Coordinator<br>European Molecular Biology Laboratory<br>European Bioinformatics Institute<br>Wellcome Trust Genome Campus<br>Hinxton, Cambridge<br>CB10 1SD, United Kingdom<br>Tel: +44-(0)1223-492538<br>Fax: +44-(0)1223-494468<br>Skype: andrewyatz<br><a href="http://www.ensembl.org/"><span style='color:purple'>http://www.ensembl.org/</span></a><o:p></o:p></p></div></div><div><p class=MsoNormal style='background:white'> <o:p></o:p></p></div><div><blockquote style='margin-top:5.0pt;margin-bottom:5.0pt'><div><div><p class=MsoNormal style='background:white'>On 16 Dec 2014, at 16:40, Rob Sargent <<a href="mailto:rob.sargent@utah.edu"><span style='color:purple'>rob.sargent@utah.edu</span></a>> wrote:<o:p></o:p></p></div></div><div><p class=MsoNormal style='background:white'> <o:p></o:p></p></div><div><div><p class=MsoNormal style='margin-bottom:12.0pt;background:white'><span style='font-size:10.0pt;font-family:"Courier New"'>A function/stored-procedure using a recursive CTE might be the way for Steve to go.</span><o:p></o:p></p><div><div><p class=MsoNormal style='background:white'>On 12/16/2014 09:28 AM, Andrew Yates wrote:<o:p></o:p></p></div></div><blockquote style='margin-top:5.0pt;margin-bottom:5.0pt'><div><p class=MsoNormal style='background:white'>Hey Steve,<span class=apple-converted-space> </span><o:p></o:p></p></div><div><div><p class=MsoNormal style='background:white'> <o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'>The problem with using the database is that sequence is not stored against the top-level sequences annotation is held against. Instead sequence is held against the contig sequence regions which requires descending through the assembly table an unspecified number of times (once for each mapping e.g. chromosome -> supercontig -> contig). <o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'> <o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'>I would seriously *not* recommend doing this. Not only do you have to deal with descending down the assembly but also having to think about concatenating the sequence & paying attention to the orientation of assembly. Instead you could use the Perl API (probably not an option considering you’re a Python guy), BioMart (you can access unspliced gene sequence quite easily), the REST API or download the full genome sequence from FTP and doing subslices. The faindex index tool from htslib/samtools is pretty good at extracting arbitrary sequence from very large FASTA files.<o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'> <o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'>Andy<o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'> <o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'>------------<br>Andrew Yates - Ensembl Support Coordinator<br>European Molecular Biology Laboratory<br>European Bioinformatics Institute<br>Wellcome Trust Genome Campus<br>Hinxton, Cambridge<br>CB10 1SD, United Kingdom<br>Tel: +44-(0)1223-492538<br>Fax: +44-(0)1223-494468<br>Skype: andrewyatz<br><a href="http://www.ensembl.org/"><span style='color:purple'>http://www.ensembl.org/</span></a><o:p></o:p></p></div></div><div><p class=MsoNormal style='background:white'> <o:p></o:p></p></div><div><blockquote style='margin-top:5.0pt;margin-bottom:5.0pt'><div><div><p class=MsoNormal style='background:white'>On 16 Dec 2014, at 16:15, Steve Moss <<a href="mailto:gawbul@gmail.com"><span style='color:purple'>gawbul@gmail.com</span></a>> wrote:<o:p></o:p></p></div></div><div><p class=MsoNormal style='background:white'> <o:p></o:p></p></div><div><div><div><p class=MsoNormal style='background:white'><span style='font-size:10.0pt'>Dear EnsEMBL Dev,</span><o:p></o:p></p></div><div><div><div><p class=MsoNormal style='background:white'><span style='font-size:10.0pt'> </span><o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'><span style='font-size:10.0pt'>I'm trying to write a raw SQL query to retrieve the sequence for the human BRCA2 gene to compare different methods of accessing EnsEMBL data. I'm currently doing the following, but getting an empty set.</span><o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'><span style='font-size:10.0pt'> </span><o:p></o:p></p></div></div><div><div><div><p class=MsoNormal style='background:white'><span style='font-size:10.0pt'>SELECT SUBSTRING(sequence, g.seq_region_start, g.seq_region_end)</span><o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'><span style='font-size:10.0pt'>FROM dna d</span><o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'><span style='font-size:10.0pt'>JOIN gene g</span><o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'><span style='font-size:10.0pt'>ON d.seq_region_id = g.seq_region_id</span><o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'><span style='font-size:10.0pt'>WHERE g.stable_id="ENSG00000139618"</span><o:p></o:p></p></div></div></div><div><div><p class=MsoNormal style='background:white'><span style='font-size:10.0pt'> </span><o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'><span style='font-size:10.0pt'>What am I missing? I think I'm falling short on working out the coord. system mapping stuff. Any pointers to help in fixing please?</span><o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'><span style='font-size:10.0pt'> </span><o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'><span style='font-size:10.0pt'>Cheers,</span><o:p></o:p></p></div></div><div><div><p class=MsoNormal style='background:white'><span style='font-size:10.0pt'><br>Steve</span><o:p></o:p></p></div></div></div><div><div><p class=MsoNormal style='background:white'> <o:p></o:p></p></div></div><div><p class=MsoNormal style='background:white'>--<span class=apple-converted-space> </span><o:p></o:p></p></div><div><div><table class=MsoNormalTable border=0 cellspacing=0 cellpadding=0 style='outline: 0px;font-style:inherit;border-spacing: 0px'><tr style='height:22.5pt;outline: 0px;font-style:inherit'><td valign=bottom style='padding:0in 0in 0in 0in;height:22.5pt;outline: 0px;font-style:inherit'><div><p class=MsoNormal><span style='font-size:1.0pt'> </span><o:p></o:p></p></div></td></tr><tr style='outline: 0px;font-style:inherit'><td valign=top style='padding:0in 0in 0in 0in;outline: 0px;font-style:inherit'><div><div><p class=MsoNormal style='vertical-align:baseline'><b><span style='font-size:13.5pt;color:#333333'>Steve Moss</span></b><o:p></o:p></p></div></div><div style='margin-top:2.25pt;outline: 0px;font-style:inherit'><div><p class=MsoNormal style='vertical-align:baseline'><span style='font-size:9.0pt'><a href="http://about.me/gawbul">about.me/gawbul</a></span><o:p></o:p></p></div></div></td></tr><tr style='outline: 0px;font-style:inherit'><td valign=top style='padding:6.0pt 0in 0in 0in;outline: 0px;font-style:inherit'><p class=MsoNormal align=right style='text-align:right;background:#C5D0E0;vertical-align:baseline'><img border=0 width=88 height=4 id="_x0000_i1025" src="http://d13pix9kaak6wt.cloudfront.net/signature/colorbar.png" alt="Steve Moss on about.me"><o:p></o:p></p></td></tr><tr style='height:15.0pt;outline: 0px;font-style:inherit'><td valign=bottom style='padding:0in 0in 0in 0in;height:15.0pt;outline: 0px;font-style:inherit'><div><p class=MsoNormal><span style='font-size:1.0pt'> </span><o:p></o:p></p></div></td></tr></table></div></div></div><div><p class=MsoNormal style='background:white'>_______________________________________________<br>Dev mailing list <a href="mailto:Dev@ensembl.org"><span style='color:purple'>Dev@ensembl.org</span></a><br>Posting guidelines and subscribe/unsubscribe info:<span class=apple-converted-space> </span><a href="http://lists.ensembl.org/mailman/listinfo/dev"><span style='color:purple'>http://lists.ensembl.org/mailman/listinfo/dev</span></a><br>Ensembl Blog:<span class=apple-converted-space> </span><a href="http://www.ensembl.info/"><span style='color:purple'>http://www.ensembl.info/</span></a><o:p></o:p></p></div></div></blockquote></div><div><p class=MsoNormal style='background:white'><br><br><br><br><br><o:p></o:p></p></div><pre style='background:white'>_______________________________________________<o:p></o:p></pre><pre style='background:white'>Dev mailing list <a href="mailto:Dev@ensembl.org"><span style='color:purple'>Dev@ensembl.org</span></a><o:p></o:p></pre><pre style='background:white'>Posting guidelines and subscribe/unsubscribe info: <a href="http://lists.ensembl.org/mailman/listinfo/dev"><span style='color:purple'>http://lists.ensembl.org/mailman/listinfo/dev</span></a><o:p></o:p></pre><pre style='background:white'>Ensembl Blog: <a href="http://www.ensembl.info/"><span style='color:purple'>http://www.ensembl.info/</span></a><o:p></o:p></pre></blockquote><div><p class=MsoNormal style='background:white'> <o:p></o:p></p></div></div><div><p class=MsoNormal style='background:white'>_______________________________________________<br>Dev mailing list <a href="mailto:Dev@ensembl.org"><span style='color:purple'>Dev@ensembl.org</span></a><br>Posting guidelines and subscribe/unsubscribe info:<span class=apple-converted-space> </span><a href="http://lists.ensembl.org/mailman/listinfo/dev"><span style='color:purple'>http://lists.ensembl.org/mailman/listinfo/dev</span></a><br>Ensembl Blog:<span class=apple-converted-space> </span><a href="http://www.ensembl.info/"><span style='color:purple'>http://www.ensembl.info/</span></a><o:p></o:p></p></div></div></blockquote></div><div><p class=MsoNormal style='background:white'> <o:p></o:p></p></div></div><div><p class=MsoNormal style='background:white'><br><br><br><br><o:p></o:p></p></div><pre style='background:white'>_______________________________________________<o:p></o:p></pre><pre style='background:white'>Dev mailing list <a href="mailto:Dev@ensembl.org"><span style='color:purple'>Dev@ensembl.org</span></a><o:p></o:p></pre><pre style='background:white'>Posting guidelines and subscribe/unsubscribe info: <a href="http://lists.ensembl.org/mailman/listinfo/dev"><span style='color:purple'>http://lists.ensembl.org/mailman/listinfo/dev</span></a><o:p></o:p></pre><pre style='background:white'>Ensembl Blog: <a href="http://www.ensembl.info/"><span style='color:purple'>http://www.ensembl.info/</span></a><o:p></o:p></pre></blockquote><div><p class=MsoNormal style='background:white'> <o:p></o:p></p></div><p class=MsoNormal><span style='font-size:9.0pt;font-family:"Helvetica","sans-serif";background:white'>_______________________________________________</span><span style='font-size:9.0pt;font-family:"Helvetica","sans-serif"'><br><span style='background:white'>Dev mailing list <a href="mailto:Dev@ensembl.org">Dev@ensembl.org</a></span><br><span style='background:white'>Posting guidelines and subscribe/unsubscribe info: <a href="http://lists.ensembl.org/mailman/listinfo/dev">http://lists.ensembl.org/mailman/listinfo/dev</a></span><br><span style='background:white'>Ensembl Blog: <a href="http://www.ensembl.info/">http://www.ensembl.info/</a></span></span><o:p></o:p></p></div></blockquote></div><p class=MsoNormal><o:p> </o:p></p></div></div></body></html>