Sorting data for Cursor

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

Sorting data for Cursor

dawwin
Hi,
I have a query which returns a lot of records from database (about 4 millions). I wanted to use Cursor to process them one by one, but I found one sentence in the docs which is bothering me:

Cursor SQL queries must be ordered (resultOrdered="true") using the id columns of the resultMap

Does this mean that I have to add ORDER BY to my query? I would like to avoid that, because it would significantly increase execution time of my query. I don't need this data to be sorted.

Is there any particular reason, why this has to be ordered? Is there a way to do this without ordering?

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Sorting data for Cursor

Erwan Letessier
I think this is a constraint applicable when using the option resultOrdered.
Then it may be ignored when you don't need it.

On Wed, Sep 19, 2018, 18:51 dawwin <[hidden email]> wrote:
Hi,
I have a query which returns a lot of records from database (about 4 millions). I wanted to use Cursor to process them one by one, but I found one sentence in the docs which is bothering me:

Cursor SQL queries must be ordered (resultOrdered="true") using the id columns of the resultMap

Does this mean that I have to add ORDER BY to my query? I would like to avoid that, because it would significantly increase execution time of my query. I don't need this data to be sorted.

Is there any particular reason, why this has to be ordered? Is there a way to do this without ordering?

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Sorting data for Cursor

dawwin
Even if that is the case, what is the reason to order by id? What if I wanted to order by priority or any other column that is not ID. I'm just curious where this requirement came from, because I don't see anything in the mybatis source code that would justify this.

On Wednesday, September 19, 2018 at 7:45:35 PM UTC+2, Erwan Letessier wrote:
I think this is a constraint applicable when using the option resultOrdered.
Then it may be ignored when you don't need it.

On Wed, Sep 19, 2018, 18:51 dawwin <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="r7qWMfhmCgAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">daw.wi...@...> wrote:
Hi,
I have a query which returns a lot of records from database (about 4 millions). I wanted to use Cursor to process them one by one, but I found one sentence in the docs which is bothering me:

Cursor SQL queries must be ordered (resultOrdered="true") using the id columns of the resultMap

Does this mean that I have to add ORDER BY to my query? I would like to avoid that, because it would significantly increase execution time of my query. I don't need this data to be sorted.

Is there any particular reason, why this has to be ordered? Is there a way to do this without ordering?

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to <a href="javascript:" rel="nofollow" target="_blank" gdf-obfuscated-mailto="r7qWMfhmCgAJ" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">mybatis-user...@googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Tim
Reply | Threaded
Open this post in threaded view
|

Re: Sorting data for Cursor

Tim
You can report an issue against the verbage but it's really easy to try to see if it works for you.
I use it without an order by 'id' and it's fine. If it doesn't work create a github issue with a sample project and we will look into it.



On Mon, Sep 24, 2018 at 3:31 AM dawwin <[hidden email]> wrote:
Even if that is the case, what is the reason to order by id? What if I wanted to order by priority or any other column that is not ID. I'm just curious where this requirement came from, because I don't see anything in the mybatis source code that would justify this.

On Wednesday, September 19, 2018 at 7:45:35 PM UTC+2, Erwan Letessier wrote:
I think this is a constraint applicable when using the option resultOrdered.
Then it may be ignored when you don't need it.

On Wed, Sep 19, 2018, 18:51 dawwin <[hidden email]> wrote:
Hi,
I have a query which returns a lot of records from database (about 4 millions). I wanted to use Cursor to process them one by one, but I found one sentence in the docs which is bothering me:

Cursor SQL queries must be ordered (resultOrdered="true") using the id columns of the resultMap

Does this mean that I have to add ORDER BY to my query? I would like to avoid that, because it would significantly increase execution time of my query. I don't need this data to be sorted.

Is there any particular reason, why this has to be ordered? Is there a way to do this without ordering?

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Sorting data for Cursor

Iwao AVE!
In reply to this post by dawwin
Order matters when the result map contains <collection />.
Try changing the ORDER BY clause in the test.
/src/test/java/org/apache/ibatis/submitted/cursor_nested

Regards,
Iwao
On Mon, Sep 24, 2018 at 4:31 PM dawwin <[hidden email]> wrote:

>
> Even if that is the case, what is the reason to order by id? What if I wanted to order by priority or any other column that is not ID. I'm just curious where this requirement came from, because I don't see anything in the mybatis source code that would justify this.
>
> On Wednesday, September 19, 2018 at 7:45:35 PM UTC+2, Erwan Letessier wrote:
>>
>> I think this is a constraint applicable when using the option resultOrdered.
>> Then it may be ignored when you don't need it.
>>
>> On Wed, Sep 19, 2018, 18:51 dawwin <[hidden email]> wrote:
>>>
>>> Hi,
>>> I have a query which returns a lot of records from database (about 4 millions). I wanted to use Cursor to process them one by one, but I found one sentence in the docs which is bothering me:
>>>
>>>> Cursor SQL queries must be ordered (resultOrdered="true") using the id columns of the resultMap
>>>
>>>
>>> Does this mean that I have to add ORDER BY to my query? I would like to avoid that, because it would significantly increase execution time of my query. I don't need this data to be sorted.
>>>
>>> Is there any particular reason, why this has to be ordered? Is there a way to do this without ordering?
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups "mybatis-user" group.
>>> To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
>>> For more options, visit https://groups.google.com/d/optout.
>
> --
> You received this message because you are subscribed to the Google Groups "mybatis-user" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
> For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Sorting data for Cursor

dawwin
Thanks for explanation Iwao. It makes perfect sense to enforce ordering when using <collection/> .
It was unclear for me because SQLs in org.apache.ibatis.submitted.cursor_simple are ordered as well.
Would this be ok for you if I create a pull request with updated comments for Cursor class and remove ordering from cursor_simple tests?

On Monday, September 24, 2018 at 7:48:29 PM UTC+2, Iwao AVE! wrote:
Order matters when the result map contains <collection />.
Try changing the ORDER BY clause in the test.
/src/test/java/org/apache/ibatis/submitted/cursor_nested

Regards,
Iwao
On Mon, Sep 24, 2018 at 4:31 PM dawwin <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="_tIsHEYgAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">daw.wi...@...> wrote:

>
> Even if that is the case, what is the reason to order by id? What if I wanted to order by priority or any other column that is not ID. I'm just curious where this requirement came from, because I don't see anything in the mybatis source code that would justify this.
>
> On Wednesday, September 19, 2018 at 7:45:35 PM UTC+2, Erwan Letessier wrote:
>>
>> I think this is a constraint applicable when using the option resultOrdered.
>> Then it may be ignored when you don't need it.
>>
>> On Wed, Sep 19, 2018, 18:51 dawwin <[hidden email]> wrote:
>>>
>>> Hi,
>>> I have a query which returns a lot of records from database (about 4 millions). I wanted to use Cursor to process them one by one, but I found one sentence in the docs which is bothering me:
>>>
>>>> Cursor SQL queries must be ordered (resultOrdered="true") using the id columns of the resultMap
>>>
>>>
>>> Does this mean that I have to add ORDER BY to my query? I would like to avoid that, because it would significantly increase execution time of my query. I don't need this data to be sorted.
>>>
>>> Is there any particular reason, why this has to be ordered? Is there a way to do this without ordering?
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups "mybatis-user" group.
>>> To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
>>> For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.
>
> --
> You received this message because you are subscribed to the Google Groups "mybatis-user" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="_tIsHEYgAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">mybatis-user...@googlegroups.com.
> For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Sorting data for Cursor

Iwao AVE!
Sure. Thank you!

On Sun, Oct 28, 2018 at 9:41 PM dawwin <[hidden email]> wrote:
>
> Thanks for explanation Iwao. It makes perfect sense to enforce ordering when using <collection/> .
> It was unclear for me because SQLs in org.apache.ibatis.submitted.cursor_simple are ordered as well.
> Would this be ok for you if I create a pull request with updated comments for Cursor class and remove ordering from cursor_simple tests?

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.