Re: ole db/400 performance large file

["Jim Franz" <franz400@xxxxxxxxxxxx>]

The network server is local. They are getting the file via ftp, then 
integrate it into their
SQL database. Later a windows process (i don't know if .net or some other MS 
tool)
makes a selection & then pushes the data via the OLE connection - I've seen 
the execution
and the OLE  part is just a box on a graphic display that turns green while 
it executes.
I was not thinking OLE was record by record (i've never worked with it).
I see your point & will talk to the network pgmr who set this up.
Jim

----- Original Message ----- 
From: "Evan Harris" <spanner@xxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
Sent: Wednesday, January 13, 2010 10:16 PM
Subject: RE: ole db/400 performance large file


> What do you mean by network server then - something that's local on your
> network, or are we talking a canned piece of software ?
>
> Stored proc was the kind of thing I was thinking when I mentioned 
> triggers.
>
> Does the file get rebuilt and cleared every day ?
>
> If so, you could consider pushing the file into some other format (csv, 
> xml,
> something else) and pushing that over the network then parsing it at the
> iseries end.
>
> The idea of a record by record transfer is what is (probably) at the root 
> of
> your problem.
>
> Regards
> Evan Harris
>
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jim Franz
> Sent: Thursday, 14 January 2010 4:05 p.m.
> To: Midrange Systems Technical Discussion
> Subject: Re: ole db/400 performance large file
>
> So far in this discussion I think my best bet is going to be ftp (which 
> has
> fewer layers to push this thru) or possible stored procedure.
> We don't have any choices in the received file & it's format. It's coming
> from another entity, not related, and basically "this is what you get".
> jim
>
> ----- Original Message ----- 
> From: "Evan Harris" <spanner@xxxxxxxxxx>
> To: "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
> Sent: Wednesday, January 13, 2010 9:30 PM
> Subject: RE: ole db/400 performance large file
>
>
> > Transferring of a large number of records is always a hassle, particular
> > when you start going across machine boundaries and across networks. I
> > guess
> > ASCII->EBCDIC can't help either.
> >
> > Seems to me that a better option would be to revisit the logic of
> > accumulating the records for a bulk transfer.
> >
> > Possible options:
> >
> > - Write directly to the iseries instead of an intermediate database and
> > run
> > in real time
> > - Use a data queue to add the records and run in real time
> > - Use a Web Service to add the records and run in real time
> > - Re-engineer the database to send each record separately using some kind
> > of
> > add trigger
> > - Batch the records up somehow (Ugh)
> >
> > God luck optimizing this. Personally I don't see it as a performance
> > issue,
> > it's a design issue. The reason it is slow is because you are moving
> > 500,000
> > records though a heck of a lot of layers. If the developer foresaw or
> > planned for the number of records involved the transfer process would 
> > have
> > been tested *before* hitting production. An all too common scenario.
> >
> > Regards
> > Evan Harris
> >
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jim Franz
> > Sent: Thursday, 14 January 2010 11:29 a.m.
> > To: MIDRANGE-L@xxxxxxxxxxxx
> > Subject: ole db/400 performance large file
> >
> > I've search the archives for this & not found a good answer.
> > One of the network programmers is using the iSeries Access V5R4
> > OLE DB connection to move 500,000 records from a network server
> > to the System i (v5r4). The native file has no logicals, and no key.
> > It is taking many minutes to load the first 10,000 and hours to load
> > 500,000.
> > Local lan connection. Cannot see why so slow.
> > File not in use on either end. Not a huge record layout.
> > How can we speed this up. It needs to run on a regular basis (weekly or
> > even
> > daily).
> > We have considered ftp, but that server is off on the production server.
> > Same OLE connection used for many applications, but those are generally
> > random access, and no problems. Only the large file is a problem.
> > Verified the network side is accumulating the records very fast.
> > Jim Franz
> > --
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.