Batched statements not working in Oracle
58 views
Skip to first unread message
Shane StClair
Jan 20, 2012, 5:09:02 PM1/20/12
to scrip...@googlegroups.com
Using Oracle's ojdbc6 driver 11.2.0.3 against db version 11.2.0.1.0, batched statements don't seem to be working. I'm trying to transfer a table with 550K+ records. The number of records in the table after the ETL completes is usually non-zero but always smaller than the batch size, which suggests that maybe only the statements queued when the script completes are flushed/committed. All records are successfully transferred when using the same ETL with SQL Server as the destination database.
Should I make this a ticket in the issue tracker?
Fyodor Kupolov
Jan 22, 2012, 3:02:12 AM1/22/12
to Scriptella ETL
Yes, please make a ticket and provide a script reproducing a problem,
including connection parameters.
including connection parameters.
Fyodor Kupolov
Jan 22, 2012, 3:07:34 AM1/22/12
to Scriptella ETL
Based on your input, I can think that there might be a problem in a
new batching mechanism. Although I was told that the new feature works
OK on Oracle http://scriptella.wordpress.com/2010/08/14/support-for-jdbc-batching-and-query-fetch-has-been-added/#comment-35
. I'll hold off my commit for statement.batchSize until we get a more
clear picture.
new batching mechanism. Although I was told that the new feature works
OK on Oracle http://scriptella.wordpress.com/2010/08/14/support-for-jdbc-batching-and-query-fetch-has-been-added/#comment-35
. I'll hold off my commit for statement.batchSize until we get a more
clear picture.
Shane StClair
Jan 22, 2012, 8:44:41 PM1/22/12
to scrip...@googlegroups.com
Hmm...I'm not sure what I was doing wrong before, but it seems to be working now. I must have had some configuration wrong or something, because it was consistently failing before. So, please disregard for now, and if I get it to fail consistently again I'll submit a ticket with an ETL.
Shane StClair
Jan 22, 2012, 8:45:56 PM1/22/12
to scrip...@googlegroups.com
Oh, and I should add that using statement.fetchSize = 1000 and statement.batchSize = 500 brought my ETL time down from 13 hours to 1.75 hours.
Fyodor Kupolov
Jan 25, 2012, 4:31:41 AM1/25/12
to Scriptella ETL
Thanks for sharing these numbers, they look great!
BTW Changes for flushBeforeQuery are already in svn. I would also
suggest to try setting statement.fetchSize=1000 for a source
connection. See http://scriptella.javaforge.com/docs/api/scriptella/jdbc/package-summary.html#package_description
BTW Changes for flushBeforeQuery are already in svn. I would also
suggest to try setting statement.fetchSize=1000 for a source
connection. See http://scriptella.javaforge.com/docs/api/scriptella/jdbc/package-summary.html#package_description
Fyodor Kupolov
Jan 25, 2012, 1:44:56 PM1/25/12
to scrip...@googlegroups.com, Scriptella ETL
I've missed that you've already specified fetchSize... you can also try setting autocommit.size to some reasonable value like 10000. I recall some db have issue with long transactions.
Sent from my phone, please forgive typos...
> --
> You received this message because you are subscribed to the Google Groups "Scriptella ETL" group.
> To post to this group, send email to scrip...@googlegroups.com.
> To unsubscribe from this group, send email to scriptella+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/scriptella?hl=en.
>
Reply all
Reply to author
Forward
0 new messages