Serious performance issue with our DR cloud site for iSeries AS400 ETL testing.

Question

Post reply

Serious performance issue with our DR cloud site for iSeries AS400 ETL testing.

TangoVictor

SSChampion

Points: 10975
More actions
March 14, 2022 at 9:30 pm

#3999407
We're doing a DR test and have ETL jobs that are running as much 2000% more duration with our DR cloud provider.
An ad-hoc ODBC query complete in seconds but an ETL doing the same thing takes 30 minutes.
We've narrowed the issue down to the OLE for ADO connections that many of our packages use. Native ADO.Net and Native OLE run fine.
Connection manager type and connection string:
```
ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

Data Source=xxxxxxxx;User ID=xxxxxxxx;Initial Catalog=xxxxxxx;Provider=IBMDA400.DataSource.1;Persist Security Info=True;
```
Bandwidth is not an issue, we confirmed that. The only difference between our DR site and our local AS400 is the latency, 28ms vs <1 ms.
I'm assuming this is the cause and that the connector we are using is doing some kind of error checking or something that is causing round trips and that latency is piling up and killing us.
Our current option is to change connectors in every project which is a good option, but will be a significant effort.
Any other thoughts or suggestions on this? Thx.
- This topic was modified 2 years, 9 months ago by TangoVictor.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80325 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

TangoVictor SSChampion Points: 10975 More actions · Answer 2

Resolution:

What we found is this process appears to be fetching rows one at a time. The number of rows multiplied by the latency seemed to confirm this hypothesis.

We played around with connection string parameters and found that specifying block size and read only connection=true fixed the issues for most of our ETLs.

Again this was limited to the ADO.Net connection type that uses AS400 DB2 OLEDB providers.

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 3

Awesome. Thank you for the comeback on your own post. We may be going through something like what you did in the very near future and I've just book marked this post. Thank YOU!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TangoVictor SSChampion Points: 10975 More actions · Answer 4

Yeah your welcome Jeff! You've helped me quite a bit over the years so I'm glad this is helpful.

We did speak with an IBM engineer and he was going to look for any additional info that may help, if we hear anything back I'll post it here.

And likewise if you run into anything you wouldn't mind sharing we'd certainly be interested. Thx

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 5

Something surprising shot people in the head on our AS400's prior to them upgrading to the new "Power Systems", also by IBM. They lasted a really long time and, one day they started getting slower. Within about 24 hours, they were running at a snail's pace. You'll never believe what it was... they forgot to change the batteries on the cache. Seriously. They put in the new batteries and badda-bing, badda zoom! We were back up and running at the normal speeds.

And, thank you for the very kind feedback. Today was a toughy and you just mad my day.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)