Re: Batch question.

[Albert L. Sapp]

Thanks for understanding.   :-)

I could go on for a while about this, but it would probably start 
irritating the readers of this list.  I have found this list to be one 
of the bright spots in this whole project.  So to all you people who 
along with Larry respond to sometimes frustratingly simple and/or 
repetitive questions.  THANK YOU!  :-)

I started out as a not-the-top-of-the-class Cobol programmer 24 years 
ago and I find you all are helping me to keep up with the youngsters.

Keep up the good work.

Al

Larry Meadors wrote:
Heh, I know this battle well, I was there a year or two ago: "not be
locked into anything that is DB specific".

FYI: I am not ranting at you, I am ranting WITH you! :-)

It sounds like in your case it was not your decision, so you may just
be outta luck, if that is the case, that sucks. I am truly sorry to
hear that.

IMO, that sort of mandate (and it usually comes from someone who
doesn't have to deal with the implications of it) is just plain
retarded. It turns the last 30+ years of RDBMS optimization into a
total waste of time - all databases are now just data stores...why use
Oracle instead of MySQL or FoxPro?

The kicker is that you WILL use Oracle specific things in your 
application:
- What other database uses the varchar2 type?
- Will you do any /* hints for query optimization*/?
- Will you structure your SQL so that Oracle can optimize it better?
- Will you use generated primary keys?

If you don't, then you just wasted $10-$30k for Oracle licenses
instead of using something cheaper or free...

Larry


On 7/27/06, Albert L. Sapp <[EMAIL PROTECTED]> wrote:
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
>> > >
>> > >
>> >
>> >
>> >
>>
>>
>>
>>
>