PreparedStatement.executBatch()

[jane_lj]

Hi,

We have the View Model VDB which created in Teiid Designer deployed into Teiid JBoss server, this VDB supports the updates feature by writing FOR EACH ROW procedure for INSERT.

When we test the INSERT, we want to insert 2 rows, so we use PreparedStatement.executBatch() against this VM VDB, but we found my translator got two calls for INSERT, not one batch INSERT call.

Looks like Teiid Designer transformation procedure splits the batch all into 2 INSERT calls, is that true?

If yes, how to make the procedure send the batch INSERT call to my translator, not split them?

Thanks.

[shawkins]

> Looks like Teiid Designer transformation procedure splits the batch all into 2 INSERT calls, is that true?

That is unrelated to Teiid Designer.  The default processing model is to simply perform the execution for each row, an enhancment would be required for Teiid to detect situations in which intermediate updates are not needed such that the updates can be grouped.

> how to make the procedure send the batch INSERT call to my translator, not split them?

For batching to be used currently, you would either need to rely on the inherent update logic (which can batch in some circumstances, although this likely doesn't apply to your scenario since you are using a for each row procedure) or use a more general stored procedure to perform a bulk insert using a query expression.

Steve

[jane_lj]

Thank you, Steven.

[jane_lj]

Steven,

It's been 4 months since we talked about this issue.

As you said, "The default processing model is to simply perform the execution for each row, an enhancment would be required for Teiid to detect situations in which intermediate updates are not needed such that the updates can be grouped."

Is it already enhanced to the latest released Teiid version or not?

If not, I will submit a defect for it. We are waiting for this feature.

Thanks a lot.

Jing

[shawkins]

You had already submitted [#TEIID-2495] Detect situations in which intermediate updates are not needed such that the updates can be grouped - JBos… did you follow that issue?

It was worked for 8.5 to cover simplistic situations, such as when the for each row procedure that performs a single insert.  So it depends on the particulars of your trigger as to whether this will work for you in 8.5.

Beyond this we could implement something for automatic handling of multiple update statements (e.g. "for each row begin atomic insert into ...; insert into ...; end") in 8.6 without too much effort, but general case handling is more complex than I would tackle at this point - and I would opt instead for allow sql server style instead of triggers which can access the entire modification set rather than being for just each row.

Steve

[jane_lj]

Thank you, Steven. This ticket doesn't show in my account somehow.

Appreciate your efforts for resolving it. We will try it out when we are ready for 8.5.

[jane_lj]

TEIID-2495  is the defect ticket number to resolve this issue. It has been resolved in Teiid 8.5.

Here is the link:

[#TEIID-2495] Detect situations in which intermediate updates are not needed such that the updates can be grouped - JBos…