You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by kovy <st...@ext.ec.europa.eu> on 2007/02/02 10:44:35 UTC
spring + ibatis + oracle
hey everyone,
I'm having a performance problem. I need to insert around 50000 records in
one time. These insert statement can contain CLOB values with over more then
4000 characters.
It takes up to 2 hours to do this procedure.
Is this normal??? Or what is the best way to do this???
I can't go explaining my architecture completelly, the method which executes
this functionality is transactional
and the SQL map method is transactional too.
thnx
Stefaan
--
View this message in context: http://www.nabble.com/spring-%2B-ibatis-%2B-oracle-tf3159908.html#a8764127
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: spring + ibatis + oracle
Posted by Giovanni Cuccu - CUP2000 <gi...@cup2000.it>.
> Well, it's rather unrelated to spring or ibatis, I bet with pure JDBC
> the problem will still be there.
>
> 50000x4000 ~ 200Mb - 2 hours may indeed be reasonable.
>
> Here's what I'd check in the first place:
>
> Single transaction for all that 200Mb means allocating large rollback
> segments etc, I'd try splitting it into smaller ones.
>
Sorry, but I think this is a bad advice. Tranasction length in Oracle
like other databases must be dictated by the application logic. If you
are using oracle and you commit too much frequently you can incur in a
performance penalty due to the continuos synchronization of the redo
logs. You als get more chance to be exposed to the infamous ora-01555
(snapshot too old) error.
The use of batches is the correct advice.
You can also check lob segment storage and tablespace.
If nothing helps you should enable tracing on the database side (10046
event)in order to see where the most of time is spent.
Giovanni
> Check whether table where insert is done is already sized properly, so
> that Oracle does not have to allocate more space during your inserts.
>
> Use statement batching.
>
> Good luck
>
>
> On 2/2/07, *kovy* <stefaan.delanghe@ext.ec.europa.eu
> <ma...@ext.ec.europa.eu>> wrote:
>
>
> hey everyone,
>
> I'm having a performance problem. I need to insert around 50000
> records in
> one time. These insert statement can contain CLOB values with over
> more then
> 4000 characters.
> It takes up to 2 hours to do this procedure.
> Is this normal??? Or what is the best way to do this???
>
> I can't go explaining my architecture completelly, the method which
> executes
> this functionality is transactional
> and the SQL map method is transactional too.
>
> thnx
> Stefaan
>
>
> --
> View this message in context:
> http://www.nabble.com/spring-%2B-ibatis-%2B-oracle-tf3159908.html#a8764127
> <http://www.nabble.com/spring-%2B-ibatis-%2B-oracle-tf3159908.html#a8764127>
> Sent from the iBATIS - User - Java mailing list archive at
> Nabble.com <http://Nabble.com>.
>
>
>
>
> On 2/2/07, *kovy * <stefaan.delanghe@ext.ec.europa.eu
> <ma...@ext.ec.europa.eu>> wrote:
>
>
> hey everyone,
>
> I'm having a performance problem. I need to insert around 50000
> records in
> one time. These insert statement can contain CLOB values with over
> more then
> 4000 characters.
> It takes up to 2 hours to do this procedure.
> Is this normal??? Or what is the best way to do this???
>
> I can't go explaining my architecture completelly, the method which
> executes
> this functionality is transactional
> and the SQL map method is transactional too.
>
> thnx
> Stefaan
>
>
> --
> View this message in context:
> http://www.nabble.com/spring-%2B-ibatis-%2B-oracle-tf3159908.html#a8764127
> <http://www.nabble.com/spring-%2B-ibatis-%2B-oracle-tf3159908.html#a8764127>
> Sent from the iBATIS - User - Java mailing list archive at
> Nabble.com <http://Nabble.com>.
>
>
--
Giovanni Cuccu
CUP 2000 Spa
Via del Borgo di S. Pietro, 90/c - 40126 Bologna
e-mail: giovanni.cuccu _at_ cup2000.it
Re: spring + ibatis + oracle
Posted by Koka Kiknadze <22...@gmail.com>.
Well, it's rather unrelated to spring or ibatis, I bet with pure JDBC the
problem will still be there.
50000x4000 ~ 200Mb - 2 hours may indeed be reasonable.
Here's what I'd check in the first place:
Single transaction for all that 200Mb means allocating large rollback
segments etc, I'd try splitting it into smaller ones.
Check whether table where insert is done is already sized properly, so that
Oracle does not have to allocate more space during your inserts.
Use statement batching.
Good luck
On 2/2/07, kovy <st...@ext.ec.europa.eu> wrote:
>
>
> hey everyone,
>
> I'm having a performance problem. I need to insert around 50000 records in
> one time. These insert statement can contain CLOB values with over more
> then
> 4000 characters.
> It takes up to 2 hours to do this procedure.
> Is this normal??? Or what is the best way to do this???
>
> I can't go explaining my architecture completelly, the method which
> executes
> this functionality is transactional
> and the SQL map method is transactional too.
>
> thnx
> Stefaan
>
>
> --
> View this message in context:
> http://www.nabble.com/spring-%2B-ibatis-%2B-oracle-tf3159908.html#a8764127
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
On 2/2/07, kovy <st...@ext.ec.europa.eu> wrote:
>
>
> hey everyone,
>
> I'm having a performance problem. I need to insert around 50000 records in
> one time. These insert statement can contain CLOB values with over more
> then
> 4000 characters.
> It takes up to 2 hours to do this procedure.
> Is this normal??? Or what is the best way to do this???
>
> I can't go explaining my architecture completelly, the method which
> executes
> this functionality is transactional
> and the SQL map method is transactional too.
>
> thnx
> Stefaan
>
>
> --
> View this message in context:
> http://www.nabble.com/spring-%2B-ibatis-%2B-oracle-tf3159908.html#a8764127
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
Re: spring + ibatis + oracle
Posted by Clinton Begin <cl...@gmail.com>.
During my attempts at the NetFlix Prize competition, I used iBATIS and was
able to insert 15,000 records per second into SQL Server using batching and
commits around every 1000 records or so.
Now, you're using Oracle and Spring, which is a lot different from my
environment, but there is probably an ideal batch size that you can find
through some simple trial and error.
Cheers,
Clinton
On 2/2/07, kovy <st...@ext.ec.europa.eu> wrote:
>
>
> hey everyone,
>
> I'm having a performance problem. I need to insert around 50000 records in
> one time. These insert statement can contain CLOB values with over more
> then
> 4000 characters.
> It takes up to 2 hours to do this procedure.
> Is this normal??? Or what is the best way to do this???
>
> I can't go explaining my architecture completelly, the method which
> executes
> this functionality is transactional
> and the SQL map method is transactional too.
>
> thnx
> Stefaan
>
>
> --
> View this message in context:
> http://www.nabble.com/spring-%2B-ibatis-%2B-oracle-tf3159908.html#a8764127
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
RE: spring + ibatis + oracle
Posted by kovy <st...@ext.ec.europa.eu>.
i am not using batches.
Could y refer me to some doc for implementing the batches
thnx
James, Steven wrote:
>
> ASre you using batches to do this?
> -----Original Message-----
> From: kovy [mailto:stefaan.delanghe@ext.ec.europa.eu]
> Sent: Freitag, 2. Februar 2007 10:45
> To: user-java@ibatis.apache.org
> Subject: spring + ibatis + oracle
>
>
> hey everyone,
>
> I'm having a performance problem. I need to insert around 50000 records
> in
> one time. These insert statement can contain CLOB values with over more
> then
> 4000 characters.
> It takes up to 2 hours to do this procedure.
> Is this normal??? Or what is the best way to do this???
>
> I can't go explaining my architecture completelly, the method which
> executes
> this functionality is transactional
> and the SQL map method is transactional too.
>
> thnx
> Stefaan
>
>
> --
> View this message in context:
> http://www.nabble.com/spring-%2B-ibatis-%2B-oracle-tf3159908.html#a87641
> 27
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
>
> This e-mail and any attachment is for authorised use by the intended
> recipient(s) only. It may contain proprietary material, confidential
> information and/or be subject to legal privilege. It should not be copied,
> disclosed to, retained or used by, any other party. If you are not an
> intended recipient then please promptly delete this e-mail and any
> attachment and all copies and inform the sender. Thank you.
>
>
--
View this message in context: http://www.nabble.com/spring-%2B-ibatis-%2B-oracle-tf3159908.html#a8764758
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.