faster updates

[netsql <ne...@roomity.com>]

So I have this application that does this one update millions of times. 
Is there a way to hook up ibatis to do prepared statements? ('cuase it 
be slower otherwise).

tia,
.V
ps: if not then I would write a thread that accepts the update requests 
to a Q, and keeps an open prepared  statement that removes from Q to do 
JDBC updates, and commits every few seconds and re-opens a prepared 
statement. Comments?

Re: faster updates

[netsql <ne...@roomity.com>]

Also, I wonder if iBatis is smart enough to create statments for each 
type of a statment if there are mutiple?

or should I have 2 sqlmaps for 2 statments?

.V

> 
>>
>> Diran Ayandele wrote:
>>> Or, you could even leave batch size to your dao:
> 
>>>
>>>        sqlMap.startBatch();
> 
>>>        return sqlMap.executeBatch();
>>>>
> 
> 

Re: faster updates

[netsql <ne...@roomity.com>]

So that is a good question: startTransaction or startBatch or both? 
Which 1st?

Which is faster?

tia,
.V

netsql wrote:
> 
> Diran Ayandele wrote:
>> Or, you could even leave batch size to your dao:

>>
>>        sqlMap.startBatch();

>>        return sqlMap.executeBatch();
>>>

Re: faster updates

[netsql <ne...@roomity.com>]

Here is what I am going to do:
Have a static warper around sqlMap and disable autocommit.

The threads then just say normal wraper.update(x); // on the static class.

Then create a timer thread that every 2 seconds calls a commit and a 
begin on the static wraper.

So static means no new objects and timer commits what ever was added in 
the last few seconds. That one update command will have it's own sqlmap, 
the rest can be in another sqlmap. In theory, it smells good.

.V


Diran Ayandele wrote:
> Or, you could even leave batch size to your dao:
> 
>    protected int executeBatch(String statementName, List objectsToLoad) {
>    SqlMapExecutor sqlMap = getSqlMapExecutor();
>    try {
>           //add simple logic to go in increments of x here.
> 
>        sqlMap.startBatch();
>        for(int i=0; i< objectsToLoad.size(); i++) {
>        sqlMap.update(statementName, objectsToLoad.get(i));
>        }
>        return sqlMap.executeBatch();
>        //   end
> 
>        throw new DaoException("Error executing batch.  Cause: " + e, e);
>    }
>    }
> 
> Nathan Maves wrote:
> 
>> Not sure if I understand your problem but here goes ...
>>
>> In my service layer I would put together a LIst of your objects to be  
>> updated.  Say maybe a 1000 at a time.  When you List is full then I  
>> would send this to the dao level and then add each object to the  
>> batch.  Once everyone of the 1000 are in the batch execute it.
>>
>> This should be way faster then individual updates even with a  
>> prepared statement.
>>
>> Nathan
>>
>> On May 10, 2006, at 3:50 PM, netsql wrote:
>>
>>> How do you batch update multiples if I discreatley call each update?
>>>
>>> Do begin trn/end tarn on a timer?
>>>
>>> .V
>>>
>>> Nathan Maves wrote:
>>>
>>>>   I would suggest trying a batch insert/update first.
>>>
>>>
>>
> 

Re: faster updates

[Diran Ayandele <Ad...@Sun.COM>]

Or, you could even leave batch size to your dao:

    protected int executeBatch(String statementName, List objectsToLoad) {
    SqlMapExecutor sqlMap = getSqlMapExecutor();
    try {
   
         //add simple logic to go in increments of x here.

        sqlMap.startBatch();
        for(int i=0; i< objectsToLoad.size(); i++) {
        sqlMap.update(statementName, objectsToLoad.get(i));
        }
        return sqlMap.executeBatch();
        //   end

        throw new DaoException("Error executing batch.  Cause: " + e, e);
    }
    }

Nathan Maves wrote:

> Not sure if I understand your problem but here goes ...
>
> In my service layer I would put together a LIst of your objects to be  
> updated.  Say maybe a 1000 at a time.  When you List is full then I  
> would send this to the dao level and then add each object to the  
> batch.  Once everyone of the 1000 are in the batch execute it.
>
> This should be way faster then individual updates even with a  
> prepared statement.
>
> Nathan
>
> On May 10, 2006, at 3:50 PM, netsql wrote:
>
>> How do you batch update multiples if I discreatley call each update?
>>
>> Do begin trn/end tarn on a timer?
>>
>> .V
>>
>> Nathan Maves wrote:
>>
>>>   I would suggest trying a batch insert/update first.
>>
>>
>

Re: faster updates

[Nathan Maves <Na...@Sun.COM>]

Not sure if I understand your problem but here goes ...

In my service layer I would put together a LIst of your objects to be  
updated.  Say maybe a 1000 at a time.  When you List is full then I  
would send this to the dao level and then add each object to the  
batch.  Once everyone of the 1000 are in the batch execute it.

This should be way faster then individual updates even with a  
prepared statement.

Nathan

On May 10, 2006, at 3:50 PM, netsql wrote:

> How do you batch update multiples if I discreatley call each update?
>
> Do begin trn/end tarn on a timer?
>
> .V
>
> Nathan Maves wrote:
>>   I would suggest trying a batch insert/update first.
>

Re: faster updates

[netsql <ne...@roomity.com>]

So reading last paragraph of sql map on page 32 docs.....
I wrap the sql map in static methods anyway.... so that would mean that 
if that sql map has only one statement, it could Q up all updates, and 
then ever 3 secods restart the transaction.
It seems to make sense.

.V

netsql wrote:
> 
> Do begin tran/end tran on a timer?
> 
> .V
> 

> 

Re: faster updates

[netsql <ne...@roomity.com>]

How do you batch update multiples if I discreatley call each update?

Do begin trn/end tarn on a timer?

.V

Nathan Maves wrote:
>   I 
> would suggest trying a batch insert/update first.  

Re: faster updates

[Nathan Maves <Na...@Sun.COM>]

First off ibatis uses prepared statements for every query/update.  I  
would suggest trying a batch insert/update first.  If this  
performance is still to slow I would go to a stored procedure.

nathan
On May 10, 2006, at 3:08 PM, netsql wrote:

> So I have this application that does this one update millions of  
> times. Is there a way to hook up ibatis to do prepared statements?  
> ('cuase it be slower otherwise).
>
> tia,
> .V
> ps: if not then I would write a thread that accepts the update  
> requests to a Q, and keeps an open prepared  statement that removes  
> from Q to do JDBC updates, and commits every few seconds and re- 
> opens a prepared statement. Comments?
>