Re: JDBC Insert Performance

[Allen <tayal01@xxxxxxxxx>]

--- Dieter Bender <Dieter.Bender@xxxxxxxxxxx> wrote:
> Paul,
> 
> if you have a look at the time, the inserts need,
> there must be another 
> problem.
> 
> Dieter

Good point Dieter and that is exactly what I am hoping
to find out.  I am finding out about database monitor
in order to try and move this forward.

Thanks,
Allen.

> 
> On Wednesday 09 July 2003 16:16, you wrote:
> > Allen,
> >
> > In terms of SQL insert performance, could you code
> your data load
> > application so it did something like a "Insert
> into lib.file (a, b, c)
> > (select a, b , c  from lib2.file2)"  ????
> >
> >  From my days as a DB2/400 developer, I know this
> would reduce the Open
> > Data Paths and much of the work could be done
> below the MI.
> >
> > You can obviously tweek your SQL code to do
> transformations of data values,
> > etc using the full power of SQL....
> >
> > Just a thought.  -Paul
> >
> > At 02:50 AM 7/9/2003 -0500, you wrote:
> > >Send JAVA400-L mailing list submissions to
> > >         java400-l@xxxxxxxxxxxx
> > >
> > >To subscribe or unsubscribe via the World Wide
> Web, visit
> > >        
> http://lists.midrange.com/mailman/listinfo/java400-l
> > >or, via email, send a message with subject or
> body 'help' to
> > >         java400-l-request@xxxxxxxxxxxx
> > >
> > >You can reach the person managing the list at
> > >         java400-l-owner@xxxxxxxxxxxx
> > >
> > >When replying, please edit your Subject line so
> it is more specific
> > >than "Re: Contents of JAVA400-L digest..."
> > >
> > >
> > >Today's Topics:
> > >
> > >    1. RE: AS/400 JDBC Insert performance (Hall,
> Philip)
> > >    2. RE: AS/400 JDBC Insert performance (Joe
> Pluta)
> > >    3. RE: AS/400 JDBC Insert performance (Allen)
> > >    4. RE: AS/400 JDBC Insert performance (Allen)
> > >    5. JDBC Connection to V4R1 (Mahmood, Anwar
> (Cognizant))
> > >    6. Re: AS/400 JDBC Insert performance (Joe
> Sam Shirah)
> > >    7. Re: AS/400 JDBC Insert performance (Allen)
> > >    8. Re: JDBC Connection to V4R1 (Dieter
> Bender)
> > >    9. Re: AS/400 JDBC Insert performance (Dieter
> Bender)
> > >   10. RE: JDBC Connection to V4R1 (Mahmood,
> Anwar (Cognizant))
> > >
> > >
> >
>
>----------------------------------------------------------------------
> > >
> > >message: 1
> > >date: Tue, 8 Jul 2003 22:07:28 -0500
> > >from: "Hall, Philip" <phall@xxxxxxxx>
> > >subject: RE: AS/400 JDBC Insert performance
> > >
> > > > I am using the JT/Open toolkit JDBC driver. 
> I'm using
> > > > Prepared Statements >\
> > >
> > >Have you compared using non-prepared statements ?
> I know for SQL server it
> > >was actually slower using prepared statements,
> than dynamically building
> > >the SQL in a string buffer for each row.
> > >
> > >Also, bulk loading is the fastest, but only
> SQLServer offers a SQL
> > >statement to do it.
> > >
> > >In DB2 you use the db2 command line and the LOAD
> sub-command, Oracle uses
> > >the sqlldr command.
> > >
> > >--phil
> > >
> > >
> > >------------------------------
> > >
> > >message: 2
> > >date: Tue, 8 Jul 2003 22:19:40 -0500
> > >from: "Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>
> > >subject: RE: AS/400 JDBC Insert performance
> > >
> > > > From: Allen
> > > >
> > > > I need to insert 180,000 rows into an AS/400
> table
> > > > within a 4 hour period.  Currently the best I
> can do
> > > > will come in at 8 hours.  I am pretty sure
> that I
> > > > should be able to at least double this speed.
> > > >
> > > > I am using the JT/Open toolkit JDBC driver.
> > >
> > >Have you tried using the RLA classes rather than
> JDBC?  RLA tends to
> > > perform better in certain circumstances, and
> inserts are one of those
> > > areas in particular where RLA excels.
> > >
> > >Joe
> > >
> > >
> > >------------------------------
> > >
> > >message: 3
> > >date: Tue, 8 Jul 2003 20:17:52 -0700 (PDT)
> > >from: Allen <tayal01@xxxxxxxxx>
> > >subject: RE: AS/400 JDBC Insert performance
> > >
> > >
> > >I am using a single connection for all the work.
> > >
> > >Cheers,
> > >Allen.
> > >
> > >--- "Bartell, Aaron L. (TC)"
> > >
> > ><ALBartell@xxxxxxxxxxxxxx> wrote:
> > > > Are you pooling your connections?
> > > >
> > > > -----Original Message-----
> > > > From: Allen [mailto:tayal01@xxxxxxxxx]
> > > > Sent: Tuesday, July 08, 2003 9:59 PM
> > > > To: j400-l
> > > > Subject: AS/400 JDBC Insert performance
> > > >
> > > >
> > > >
> > > > I need to insert 180,000 rows into an AS/400
> table
> > > > within a 4 hour period.  Currently the best I
> can do
> > > > will come in at 8 hours.  I am pretty sure
> that I
> > > > should be able to at least double this speed.
> > > >
> > > > I am using the JT/Open toolkit JDBC driver. 
> I'm
> > > > using
> > > > Prepared Statements and I'm commiting after
> each
> > > > batch
> > > > of 250 rows.  The odd thing is that batching
> up the
> > > > commits at 250 rows doesn't appear to be any
> faster
> > > > than single row commits.
> > > >
> > > > Is there anything I need to do on the AS/400
> to
> > > > improve performance?  Will changing the
> isolation
> > > > level of my JDBC connection make any
> difference?
> > > >
> > > > Thanks,
> > > > Allen.
> > > >
> > > > =====
> > > >
> > > >
> > > > __________________________________
> > > > Do you Yahoo!?
> > > > SBC Yahoo! DSL - Now only $29.95 per month!
> > > > http://sbc.yahoo.com
> > > >
> _______________________________________________
> > > > This is the Java Programming on and around the
> > > > iSeries / AS400 (JAVA400-L)
> > > > mailing list
> > > > To post a message email:
> JAVA400-L@xxxxxxxxxxxx
> > > > To subscribe, unsubscribe, or change list
> options,
> > > > visit:
> > > >
> http://lists.midrange.com/mailman/listinfo/java400-l
> 
=== message truncated ===


=====


__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com