Re: ole db/400 performance large file -- MIDRANGE-L

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.