You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@camel.apache.org by Zhemzhitsky Sergey <Se...@sberbank-cib.ru> on 2012/11/14 16:12:28 UTC
Processing large jdbc result set
Hello camel gurus,
Could you suggest a best way to process a large jdbc result set that contains, let’s say, more than 10000 rows.
What I need is to fetch all the new rows from a given table when the timer event fires.
Currently jdbc components (jdbc, sql, jpa, mybatis, etc.) do not support batching, so what I’d like to implement is something like this
from(“timer:triggerName”)
.repeat(new HasMoreRowsPredicate())
.to(“sql:select top 10 * from MyTable”)
.to(“bean:process10Rows”)
Loop EIP seems very similar to what I need, but it must be initialized with a predefined number of repetitions.
Best Regards,
Sergey
_______________________________________________________
CONFIDENTIALITY NOTICE: This email and any files attached to it may be confidential. If you are not the intended recipient you are notified that using, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited. If you have received this email in error please notify the sender and delete this email.
RE: Processing large jdbc result set
Posted by Zhemzhitsky Sergey <Se...@sberbank-cib.ru>.
Hi Christian,
Dynamic router is exactly what I need. Thanks a lot for the tip!
Best Regards,
Sergey
-----Original Message-----
From: Christian Müller [mailto:christian.mueller@gmail.com]
Sent: Thursday, November 15, 2012 2:12 AM
To: users@camel.apache.org
Subject: Re: Processing large jdbc result set
What's with the dynamic router [1]? Will this work for you?
from("direct:start")
// checks whether direct:slip should be called again
.dynamicRouter(method(DynamicRouterTest.class, "slip"));
from("direct:slip")
.to("sql:select top 10 * from MyTable")
.to(“bean:process10Rows”)
[1] http://camel.apache.org/dynamic-router.html
Best,
Christian
On Wed, Nov 14, 2012 at 4:12 PM, Zhemzhitsky Sergey < Sergey_Zhemzhitsky@sberbank-cib.ru> wrote:
> Hello camel gurus,
>
> Could you suggest a best way to process a large jdbc result set that
> contains, let’s say, more than 10000 rows.
>
> What I need is to fetch all the new rows from a given table when the
> timer event fires.
> Currently jdbc components (jdbc, sql, jpa, mybatis, etc.) do not
> support batching, so what I’d like to implement is something like this
>
> from(“timer:triggerName”)
> .repeat(new HasMoreRowsPredicate())
> .to(“sql:select top 10 * from MyTable”)
> .to(“bean:process10Rows”)
>
> Loop EIP seems very similar to what I need, but it must be initialized
> with a predefined number of repetitions.
>
> Best Regards,
> Sergey
>
>
> _______________________________________________________
> CONFIDENTIALITY NOTICE: This email and any files attached to it may be
> confidential. If you are not the intended recipient you are notified
> that using, copying, distributing or taking any action in reliance on
> the contents of this information is strictly prohibited. If you have
> received this email in error please notify the sender and delete this email.
>
>
--
Re: Processing large jdbc result set
Posted by Christian Müller <ch...@gmail.com>.
What's with the dynamic router [1]? Will this work for you?
from("direct:start")
// checks whether direct:slip should be called again
.dynamicRouter(method(DynamicRouterTest.class, "slip"));
from("direct:slip")
.to("sql:select top 10 * from MyTable")
.to(“bean:process10Rows”)
[1] http://camel.apache.org/dynamic-router.html
Best,
Christian
On Wed, Nov 14, 2012 at 4:12 PM, Zhemzhitsky Sergey <
Sergey_Zhemzhitsky@sberbank-cib.ru> wrote:
> Hello camel gurus,
>
> Could you suggest a best way to process a large jdbc result set that
> contains, let’s say, more than 10000 rows.
>
> What I need is to fetch all the new rows from a given table when the timer
> event fires.
> Currently jdbc components (jdbc, sql, jpa, mybatis, etc.) do not support
> batching, so what I’d like to implement is something like this
>
> from(“timer:triggerName”)
> .repeat(new HasMoreRowsPredicate())
> .to(“sql:select top 10 * from MyTable”)
> .to(“bean:process10Rows”)
>
> Loop EIP seems very similar to what I need, but it must be initialized
> with a predefined number of repetitions.
>
> Best Regards,
> Sergey
>
>
> _______________________________________________________
> CONFIDENTIALITY NOTICE: This email and any files attached to it may be
> confidential. If you are not the intended recipient you are notified that
> using, copying, distributing or taking any action in reliance on the
> contents of this information is strictly prohibited. If you have received
> this email in error please notify the sender and delete this email.
>
>
--
RE: Processing large jdbc result set
Posted by Zhemzhitsky Sergey <Se...@sberbank-cib.ru>.
Hi Ramkumar,
>> You mention mybatis - I guess you mean ibatis
I mean http://www.mybatis.org/core/index.html which is descendant of ibatis as the last one is no longer developed.
Hibernate batch-size attribute will not help with my issue. Moreover hibernate's batching is applicable only to one-to-many associations and I don't have any.
Also hibernate batching will execute any select statements on demand while navigating over collections and not for the root entity.
Best Regards,
Sergey Zhemzhitsky
Phone. +7 495 2580500 ext. 1246
-----Original Message-----
From: Ramkumar.Iyer@cognizant.com [mailto:Ramkumar.Iyer@cognizant.com]
Sent: Wednesday, November 14, 2012 7:39 PM
To: users@camel.apache.org
Subject: RE: Processing large jdbc result set
You mention mybatis - I guess you mean ibatis. I have never worked on it but worked on hibernate which supports batching.
http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/performance.html#performance-fetching-batch
19.1.5. Using batch fetching
Using batch fetching, Hibernate can load several uninitialized proxies if one proxy is accessed. Batch fetching is an optimization of the lazy select fetching strategy. There are two ways you can configure batch fetching: on the class level and the collection level.
Batch fetching for classes/entities is easier to understand. Consider the following example: at runtime you have 25 Cat instances loaded in a Session, and each Cat has a reference to its owner, a Person. The Person class is mapped with a proxy, lazy="true". If you now iterate through all cats and call getOwner() on each, Hibernate will, by default, execute 25 SELECT statements to retrieve the proxied owners. You can tune this behavior by specifying a batch-size in the mapping of Person:
<class name="Person" batch-size="10">...</class> Hibernate will now execute only three queries: the pattern is 10, 10, 5.
You can also enable batch fetching of collections. For example, if each Person has a lazy collection of Cats, and 10 persons are currently loaded in the Session, iterating through all persons will generate 10 SELECTs, one for every call to getCats(). If you enable batch fetching for the cats collection in the mapping of Person, Hibernate can pre-fetch collections:
<class name="Person">
<set name="cats" batch-size="3">
...
</set>
</class>
With a batch-size of 3, Hibernate will load 3, 3, 3, 1 collections in four SELECTs. Again, the value of the attribute depends on the expected number of uninitialized collections in a particular Session.
Batch fetching of collections is particularly useful if you have a nested tree of items, i.e. the typical bill-of-materials pattern. However, a nested set or a materialized path might be a better option for read-mostly trees.
-----Original Message-----
From: Zhemzhitsky Sergey [mailto:Sergey_Zhemzhitsky@sberbank-cib.ru]
Sent: Wednesday, November 14, 2012 8:42 PM
To: users@camel.apache.org
Subject: Processing large jdbc result set
Hello camel gurus,
Could you suggest a best way to process a large jdbc result set that contains, let's say, more than 10000 rows.
What I need is to fetch all the new rows from a given table when the timer event fires.
Currently jdbc components (jdbc, sql, jpa, mybatis, etc.) do not support batching, so what I'd like to implement is something like this
from("timer:triggerName")
.repeat(new HasMoreRowsPredicate())
.to("sql:select top 10 * from MyTable")
.to("bean:process10Rows")
Loop EIP seems very similar to what I need, but it must be initialized with a predefined number of repetitions.
Best Regards,
Sergey
_______________________________________________________
CONFIDENTIALITY NOTICE: This email and any files attached to it may be confidential. If you are not the intended recipient you are notified that using, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited. If you have received this email in error please notify the sender and delete this email.
This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient(s), please reply to the sender and destroy all copies of the original message. Any unauthorized review, use, disclosure, dissemination, forwarding, printing or copying of this email, and/or any action taken in reliance on the contents of this e-mail is strictly prohibited and may be unlawful.
RE: Processing large jdbc result set
Posted by Ra...@cognizant.com.
You mention mybatis - I guess you mean ibatis. I have never worked on it but worked on hibernate which supports batching.
http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/performance.html#performance-fetching-batch
19.1.5. Using batch fetching
Using batch fetching, Hibernate can load several uninitialized proxies if one proxy is accessed. Batch fetching is an optimization of the lazy select fetching strategy. There are two ways you can configure batch fetching: on the class level and the collection level.
Batch fetching for classes/entities is easier to understand. Consider the following example: at runtime you have 25 Cat instances loaded in a Session, and each Cat has a reference to its owner, a Person. The Person class is mapped with a proxy, lazy="true". If you now iterate through all cats and call getOwner() on each, Hibernate will, by default, execute 25 SELECT statements to retrieve the proxied owners. You can tune this behavior by specifying a batch-size in the mapping of Person:
<class name="Person" batch-size="10">...</class>
Hibernate will now execute only three queries: the pattern is 10, 10, 5.
You can also enable batch fetching of collections. For example, if each Person has a lazy collection of Cats, and 10 persons are currently loaded in the Session, iterating through all persons will generate 10 SELECTs, one for every call to getCats(). If you enable batch fetching for the cats collection in the mapping of Person, Hibernate can pre-fetch collections:
<class name="Person">
<set name="cats" batch-size="3">
...
</set>
</class>
With a batch-size of 3, Hibernate will load 3, 3, 3, 1 collections in four SELECTs. Again, the value of the attribute depends on the expected number of uninitialized collections in a particular Session.
Batch fetching of collections is particularly useful if you have a nested tree of items, i.e. the typical bill-of-materials pattern. However, a nested set or a materialized path might be a better option for read-mostly trees.
-----Original Message-----
From: Zhemzhitsky Sergey [mailto:Sergey_Zhemzhitsky@sberbank-cib.ru]
Sent: Wednesday, November 14, 2012 8:42 PM
To: users@camel.apache.org
Subject: Processing large jdbc result set
Hello camel gurus,
Could you suggest a best way to process a large jdbc result set that contains, let's say, more than 10000 rows.
What I need is to fetch all the new rows from a given table when the timer event fires.
Currently jdbc components (jdbc, sql, jpa, mybatis, etc.) do not support batching, so what I'd like to implement is something like this
from("timer:triggerName")
.repeat(new HasMoreRowsPredicate())
.to("sql:select top 10 * from MyTable")
.to("bean:process10Rows")
Loop EIP seems very similar to what I need, but it must be initialized with a predefined number of repetitions.
Best Regards,
Sergey
_______________________________________________________
CONFIDENTIALITY NOTICE: This email and any files attached to it may be confidential. If you are not the intended recipient you are notified that using, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited. If you have received this email in error please notify the sender and delete this email.
This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient(s), please reply to the sender and destroy all copies of the original message. Any unauthorized review, use, disclosure, dissemination, forwarding, printing or copying of this email, and/or any action taken in reliance on the contents of this e-mail is strictly prohibited and may be unlawful.