[biomart-users] Need Help Getting Sorted Results

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

[biomart-users] Need Help Getting Sorted Results

Robyn Wyrick
HI Folks, 

This is my first post to this group, so please pardon me if I am missing some etiquette. I did read the knowledge base, but could not find this information. 

I am trying to get sorted search results. I am taking over a project here at NIH, and the previous developer made an API that handled sorting after retrieving all the found records for a query. That is a major performance hit, and I see that Biomart has code to handle 'orderby'. 

My problem is that, while there is perl code for this, I don't see any documentation as to how the XML would be structured to connect with that perl code. 

Is there an "orderby" attribute in the <Query> tag? Is there an <OrderBy> tag or something? I don't see any mechanism in the code for what format it might be expecting. 

I have hacked the TableSet.pm file and can prove that a valid, local $orderby value is passed to the SQL, and results in ordered output. So there is something. But I don't imagine that all that code would be built in with no way to access it through the XML. 

Many thanks in advance. 

Robyn

--
You received this message because you are subscribed to the Google Groups "biomart-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web, visit https://groups.google.com/d/msgid/biomart-users/0f92a836-bb61-44d2-9cf6-1e4d9bb98371%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: [biomart-users] Need Help Getting Sorted Results

Syed Haider-5
Hi Robyn,

ORDER BY has a significant (SQL server side) performance impact if users can invoke this through API. BioMart does offer order by at the time of configuring the marts to allow for order by on selected columns. These are almost always (or in theory at least) indexed too. So if you are the Mart owner; try resolving it on the RDBMS level rather than on the client side. 

or else TableSet.pm like hack is the way forward.

Hope this helps,
Syed

On Wed, 7 Aug 2019, 16:27 Robyn Wyrick, <[hidden email]> wrote:
HI Folks, 

This is my first post to this group, so please pardon me if I am missing some etiquette. I did read the knowledge base, but could not find this information. 

I am trying to get sorted search results. I am taking over a project here at NIH, and the previous developer made an API that handled sorting after retrieving all the found records for a query. That is a major performance hit, and I see that Biomart has code to handle 'orderby'. 

My problem is that, while there is perl code for this, I don't see any documentation as to how the XML would be structured to connect with that perl code. 

Is there an "orderby" attribute in the <Query> tag? Is there an <OrderBy> tag or something? I don't see any mechanism in the code for what format it might be expecting. 

I have hacked the TableSet.pm file and can prove that a valid, local $orderby value is passed to the SQL, and results in ordered output. So there is something. But I don't imagine that all that code would be built in with no way to access it through the XML. 

Many thanks in advance. 

Robyn

--
You received this message because you are subscribed to the Google Groups "biomart-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web, visit https://groups.google.com/d/msgid/biomart-users/0f92a836-bb61-44d2-9cf6-1e4d9bb98371%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "biomart-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web, visit https://groups.google.com/d/msgid/biomart-users/CACLWztEVmY%3Dg2NVBZwh9aLtiaM5be3O4XiMwo_v4oAOWHEs1mw%40mail.gmail.com.