Re: Sever pulling a bazillion rows from IBMi via JDBC (I think)

Most of the time is likely due to single row inserts on the destination DB.

Need to do batch inserts. If they are actually using JDBC (which would be
slightly weird given that you've said it's going into a MS SQL DB)

For MS SQL Specifically,
https://learn.microsoft.com/en-us/sql/connect/jdbc/use-bulk-copy-api-batch-insert-operation?view=sql-server-ver17

There's a more generic way for other DBs.

Charles

On Tue, Sep 2, 2025 at 10:07 AM <smith5646midrange@xxxxxxxxx> wrote:

The bottleneck is that it is transferring 22,064,447,488 bytes of data and
inserting it into a windows server database (not sure which one).

Their SQL currently does not have an order by clause. I was actually
wondering if having "order by RRN" would give better blocking for the SQL
read portion.

It looks like they are going to have to suffer through it until they change
the process to build a delta version of the file to pull.


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Patrik
Schindler
Sent: Tuesday, September 2, 2025 11:45 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Sever pulling a bazillion rows from IBMi via JDBC (I think)

Hello,

Am 02.09.2025 um 15:51 schrieb smith5646midrange@xxxxxxxxx:

Will it be faster to omit the order by clause and just let SQL hand back

data in whatever order that it wants or would it be quicker to add an order
by clause.

In general, less work means faster processing. So, not giving an order by
will yield a performance increase. How much is hard to predict. Better
measure yourself: This is a change which can easily be implemented.

The more interesting question is: Did you have a closer look what the
eventual bottleneck is?
- IBM i creating temporary indexes/collecting a result set prior to sending
any data?
- Network (remote locations with low bandwidth and high latency and maybe
slight packet loss in between?)
- What exactly does the querying side do with previous day's data? Discard?
How quickly is this happening?
- Maybe the bottleneck is the actual insert into the querying side's
database? I/O or CPU bound?

Unless these questions are evaluated and answered, it's just guesswork to
give any advice.

:wq! PoC

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.