JDBC perfomance

[Former Member]

Hello

I have this scenario FILE to ORACLE

I have a 100.000 records in a file and using XI I want to insert in a ORACLE table.

Using the JDBC adaptor I create the XML structure to insert records and it works with a few records.

Question:

JDBC adaptor can manage a XML message with 100.000 records?

How will it be the perfomance?

Could it make XI crash?

How would you do it?

many thanks.

[nisarkhan_n · Accepted solution]

The performance might hit as there are many records, but you can split the files into multiple have less number such as 10,000 records each or use the recordsetper message option in the file adapter to do...let me also check any alternate available...

this might not crash the XI but definatley will hi the performance, what's the frequeny of this interface? also is it staright mapping? i also XI will process it but you have any particular time to uplaod the data then it might be a problem...

[prateek]

JDBC adaptor can manage a XML message with 100.000 records?

Yes. It is more dependent upon the hardware configuration of XI server.

How will it be the perfomance?

Again that depends upon the hardware config.

Could it make XI crash?

Unfortunately, yes. It is possible

How would you do it?

One obvious option is to improve the hardware config.

Use the "Disconnect from database" option in the receiver communication channel.

Better option would be file split before this processing.

Regards,

Prateek

[aashish_sinha]

Hi,

JDBC adaptor can manage a XML message with 100.000 records?

I think XI is capable to handle larger data, so there must not be any problem in this regard.May be in you case its happening coz of some illegal exception so please try to look into that or try pasting those exception.

How will it be the perfomance?

Could it make XI crash?

The database is of crucial importance for overall system performance since XI persists the messages it transports several times.

I/O Distribution

Distributing I/O evenly across available I/O channels (controllers, disks) is a key factor for optimal database configuration. Therefore, you should use as many independent channels as possible. Separate data areas from log areas (as well as from paging/swap areas).

The XI application profile is characterized by the following:

- High-volume processing in a small number of tables (about 10)

- Some tables (qRFC) with high insert/delete frequency

Message Size

Throughput in terms of bytes per second is much higher for larger messages. This is mainly due to the processing overhead for a single message. Assuming a linear model for the cost of proc-essing a single message of size, the throughput increases linearly with message size for small messages and asymptotically reaches a maximum for large messages.

Furthermore, the Integration Engine stores messages in a compressed format (compression rates for XML messages are normally in the range of 70-80%). Therefore, applications should be designed to use larger messages (if possible). On the other hand, processing larger messages causes higher memory resource consumption. Therefore, you should consider 64bit platforms as there are no ad-dress space issues to limit memory usage.

Database Load Distribution

The central Adapter Framework in an All-In-One installation (Add-In installation with all XI compo-nents) uses the same database instance as the Integration Server. In a high-volume processing sce-nario, one option is to use a separate non-central Adapter Framework instance with its own database and other resources (to be installed on a different host), thus reducing load in the central database of the XI Integration Server.

The XI Integration Server provides performance measurement based on time stamps. You can acti-vate this in transaction SXMB_ADM by setting the MEASUREMENT_LEVEL parameter in category PERF. A value greater than zero causes the Integration Engine to write a time stamp in the message at the beginning and end of each processing step. These time stamps appear in the Performance-Header header attribute of an XI message. Additionally, the MEASUREMENT_PERSIST parameter causes the timestamp data to be persisted in a separate table for evaluation.

To view the data measured, you can use the Performance Monitor in the XI Runtime Workbench. There, you can display data at a detailed time-stamp level or in an aggregated form. For information about running specific aggregation jobs to consolidate data.

You can also refer to SAP Note 812158 for XI performance.

Our way :

Normally we split large data to small chunck of data at OS level and then send it to XI. you can find many tools for this.

Hope this will help you.

Regards

Aashish Sinha

PS : reward points if helpful

[Former Member]

many thanks.

Then how can explain to the customer that XI is not very hepfull to do this kind of jobs (manage large amount of data) and they must cut the files in multiples files (maybe 10 files) or the should buy a different tool for high volumen of data, or use the Oracle Loader. They think XI is a powerfull tool

regards

ger

[aashish_sinha]

Hi ,

I don't know what is your scenario or what is your customer requirement, i think oracle loader or any other tool for splitting message or anything or load data to some target is not a good comparision for XI. As i must say, this is not only the way you can use the XI to load to target location. There must be some requirement of XI like data is coming from somewhere (rather different places or machines or applications or some other systems), you have take that data, did some transformation and converted that data to desired format in which your backend system or target system required and you are done.

If loading is the only purpose of requirement after some massagine i.e transformation, you can use some ETL tool to fulfil you requirement. In my knowledge informatica is spreety much sufficient to do all these. Data taken from xml, or SAP using RFC or directly from SAP tables and it can populated the target system after some tranformation pretty quickly.

In short, XI has its own capability and benfits over other middleware and other tools also. but as you know every application or software have some issues, and we can hope that it will be rectified soon.

regards

Aashish Sinha

[Former Member]

The source target is a SAP table and the target system is an ORACLE table. The prefer to export these records to a file and the populate the file to XI to read these records and then transform to a XML INSERT statement throught JDBC connector. Very silly interface. The interface is scheduling by week.

The cuestion is the number of records that can be thousand of them.

many thanks

Regards

Ger

[Former Member]

>>The cuestion is the number of records that can be thousand of them.

you should not have any issue to post few thousand records to oracle via JDBC,specially when the scenario runs only once a week

Thanx

Aamir

[agasthuri_doss]

Hi,

Since SAP you can make the file to trigger as per the record you desire and pass to XI with Time Interval of 30 sec continously to the receiver.

Regards

Sangeetha

[Former Member]

Hi,

XML file is always larger than text file. In this case XML file with 100K records will affect XI but it will not make it crash if it is less than hundreds of MBs (depends on configuration of your system).

I suggest to break down your records from 100k of single file to 10k of 10 files. smaller the better,

You can use SQL loader to load data if this is onetime activity and for regular huge data loading I suggest to use much safer approach in XI.

Regards,

Gourav

---

reward points if it helps you