Multiple access or multiple statement

[Former Member]

In our scenario we have multiple records coming from SAP and we have to INSERT into Database.

We have modeled a XML-SQL jdbc document format in the target data type. There is one issue that we need to clarify here.This would be regarding the occurence of the the STATEMENT node vis-a-vis the ACCESS node.Two options which we have are:

1) Make the Statement unbounded and access as occurrence 1. Generate a new statement for each record and map the access for each record.

2) Make the Statement as 1..1, and make the ACCESS as 1..unbounded. The next step would be to generate the ACCESS node as many times as the number of records to be inserted.

Is there a difference here? or is there an advanatage or disavantage among the above two options.

[prateek · Accepted solution]

If the tablename is same for all operations, use multiple access. If the names are different you will have to use multiple Statements.

Regards,

Prateek

[Former Member · Accepted solution]

Hi Prateek,

Thanks for your response.

Would just like to know the following.

We just have an INSERT case. We have just one table.

What is the difference between having the STATEMENT tab as unbounded or the access being unbounded.

Would there be muliple database queries triggered on the database in case the STATEMENT is set to unbounded?

Would a case with muliple ACCESS tags and one STATEMENT tag generate just one SQL statement.

I ask this question because we are looking at a scenario, where around 10,000 insertions may happen. performance being prime in processing the request, what would be a better option.

Regards,

Abhishek

[prateek · Accepted solution]

What is the difference between having the STATEMENT tab as unbounded or the access being unbounded.

If you have only one table with one insert statement, then there is no need to create Statement and access node as unbounded. Its occurrence could be one. If there are multiple tables to be used, you should make occurrence of Statement as unbounded. Similarly in case of multiple insert on same table, you will have to create access node as unbounded.

Would there be muliple database queries triggered on the database in case the STATEMENT is set to unbounded?

Yes.

Would a case with muliple ACCESS tags and one STATEMENT tag generate just one SQL statement.

Both will generate multiple SQL statements. Statement will create altogether a new transaction.

The problem with multiple access could be that either all insertion would be committed or all would be rolled back. And the problem with multiple Statement would be that each insert will act as a separate transaction which surely hampers performance.

Regards,

Prateek

[Former Member · Accepted solution]

Hi Prateek,

Thanks for your informations.

I think my problems could now be solved.

Regards,

Abhishek