Re: Batch question.

[Albert L. Sapp]

Larry,

I am wondering about that and I do remember you recommending this in 
other posts.  2 things might stand in the way of this.

<rant>

1.  The initial requirement "mandated" during the conception stage was 
that "we would not be locked into anything that is DB specific".  I 
imagine most DBs handle stored procedures and they may be easy to port.  
;-)     It is just anytime I have suggested using anything that has 
"proprietary" in the description or even the smell of it is jumped on 
with both feet.  The only reason I got away with using the Oracle DB was 
that I had been messing with it for a couple of years prior to the 
handing of the project over to a new team leader with a better grasp of 
what was needed.  >:o

2.  To be honest, I am still trying to learn all there is to make sure I 
am using iBatis, Struts, xml, jsp and all the other stuff introduced 
into the project by the person with a better grasp (long gone now).  So 
I am uncertain about jumping into Oracle PL/SQL right now.  I can do it, 
but it is just another place to look for problems when something doesn't 
work

</rant>

Sorry about that!

Anyway, thanks for the suggestion and I will think about it.  I imagine 
I would just past the one bean and 4 bean arrays as IN parameters to the 
stored procedure and still get my exceptions back.  I will see what I 
can find to read about doing things this way.

As always, this old dog does still try to learn new tricks.  :-D

Al

Larry Meadors wrote:
<broken-record-mode>

Can this be done with a stored procedure? Is there a ton of data
input, or is the data generated based on a set of parameters, rules,
etc...

If it is generated, you could get significant performance gains using
a stored procedure instead of a batch.

</broken-record-mode>

Larry


On 7/27/06, Albert L. Sapp <[EMAIL PROTECTED]> wrote:

 5 sub-batches isn't that bad even if the first one is a single insert
statement.  The 3rd batch is the one that will contain the most create
operations.  For each item, we could have anywhere from 2 to 10 creates.
Oh, the joy of accounting operations.  :-D

 Anyway, thank you very much for spending some time helping me 
understand
this.

 Al


 Jeff Butler wrote:

Yes - going to different tables will cause a new sub batch.  
Statements will
be grouped together only if the generated SQL exactly matches (different
values for the parameters don't cause a new sub batch).  So this is 2 
sub
batches:

insert into table1 (?, ?)
 insert into table1 (?, ?)
 insert into table2 (?, ?)
 insert into table2 (?, ?)


Jeff Butler




On 7/27/06, Albert L. Sapp <[EMAIL PROTECTED]> wrote:
>
>
> Hi, Jeff.
>
> It looks like I should look at setting this up in a batch.  
Basically, I
am doing the following.
>
> createInvoice
> createInvoiceItems in loop
> createBannerTransactions in loop
> updateInventoryItems in loop
> deleteShoppingCartItems in loop
>
> I have them grouped already.  So I should have 3 sub-batches, correct?
Will the fact that the creates go against 3 different tables force 
different
sub-batches there?  Since the batch is inside the transaction, my error
trapping should not be affected, if I understand this right.  By the 
way, I
am running against a Oracle 10g DB and using the Oracle JDBC driver.  
Since
it is all working correctly right now, I don't see adding the batch 
commands
as a problem.
>
> I also agree that performance will vary as there are just too many 
factors
with each application that can affect it.  :-)
>
> Thanks for the quick response and giving me something else to look at
implementing.
>
>
> Al
>
>
> Jeff Butler wrote:
>
> A batch may help you if you can group identical statements together in
your transaction.  iBATIS calculates "sub batches" based on the 
generated
SQL - but you cannot mix the statements at will.  If a new statement is
added to a batch that does not match the immediately preceding 
statement,
then a new sub batch is created.  For example, this is 4 sub-batches and
should yield a performance improvement:
>
> insert into table1...
> insert into table1...
> insert into table1...
> update table1...
> update table1...
> update table1...
> insert into table1...
>
> insert into table1...
> insert into table1...
>
> update table1...
> update table1...
> update table1...
>
> But the following will turn into 12 sub-batches - no performance
improvement:
>
> insert into table1...
> update table1...
> insert into table1...
> update table1...
> insert into table1...
> update table1...
> insert into table1...
> update table1...
> insert into table1...
> update table1...
> insert into table1...
> update table1...
>
> Some JDBC drivers cache prepared statements and in those cases the
differences won't be so significant.  As always with any performance
discussion, your milage may vary.
>
> Jeff Butler
>
>
>
> On 7/27/06, Albert L. Sapp < [EMAIL PROTECTED]> wrote:
> > Hi, everyone.
> >
> > Did not know whether this fit in with recent question on iBatis 
batch
> > support :-) , so I just sent a separate email.
> >
> > Is there any rule-of-thumb for determining when to use batch 
within a
> > transaction?  I have a shopping cart application and, when the 
user goes
> > through checkout, there are a number of inserts, updates and 
deletes for
> > each item in the cart that are performed.  Right now, I just put 
them
> > all between start, commit and end transaction tags.  It works 
fine this
> > way with rollback happening cleanly, when needed.  I don't think 
there
> > will ever be a large number of items at any one time to in a user's
> > shopping cart, but was wondering if it is still a "best practice" 
to put
> > inside batch tags as well.
> >
> > Always try to keep up on "best practices".  ;-)
> >
> > Thanks,
> >
> > Al
> >
> >
>
>
>