Explain Analyze update tre_combinedtestday set
compoundpercent=-13.373487168551002, totalpercent=-5.466540999057416,
dollaramount=0.9453345900094259 where id=230827
returns this:
UPDATE PUBLIC.TRE_COMBINEDTESTDAY
/* PUBLIC.TRE_COMBINEDTESTDAY.tableScan */
/* scanCount: 74005 */
SET
COMPOUNDPERCENT = -13.373487168551002,
TOTALPERCENT = -5.466540999057416,
DOLLARAMOUNT = 0.9453345900094259
WHERE ID = 230827
/*
total: 5087
TRE_COMBINEDTESTDAY.TRE_COMBINEDTESTDAY_DATA read: 5087 (100%)
*/
I switched to jdbc to test this:
PreparedStatement prep = con.prepareStatement("update tre_combinedtestday
set compoundpercent=?, totalpercent=?, dollaramount=? where id=?");
//insert 10 row data
long startTime=System.currentTimeMillis();
for (int i = 0; i<saveObjects.size(); i++){
System.out.println("ii="+i);
CombinedTestDay saveObject=saveObjects.get(i);
prep.setDouble(1, saveObject.getCompoundpercent());
prep.setDouble(2, saveObject.getTotalpercent());
prep.setDouble(3, saveObject.getTotalpercent());
prep.setInt(4, saveObject.getId());
//batch insert
//prep.addBatch();
prep.executeUpdate();
}
long endTime=System.currentTimeMillis();
long totalTime=endTime-startTime;
System.out.println("totalTime="+totalTime);
The above performed around 5000 updates. I wasn't able to capture the
totalTime because the console buffer wasn't large enough, but I was able to
count along while this line incremented:
System.out.println("ii="+i);
At the rate I was observing, i estimate it took around 4-5 minutes for
these update to complete
On Monday, October 28, 2013 11:34:57 PM UTC-7, Kartweel wrote:
>
> No Worries :). If you run Explain Analyze update tre_combinedtestday set
> compoundpercent=-13.373487168551002, totalpercent=-5.466540999057416,
> dollaramount=0.9453345900094259 where id=230827 what do you get?
>
>
>
> On 29/10/2013 2:12 PM, TrendTimer.com wrote:
>
> Kartweel, I forgot to say "thanks" for your reply. Ormlite has an option
> to perform rawQueries. So I changed it to use this. Here's the query and
> the execution time:
>
>
> query= update tre_combinedtestday set
> compoundpercent=-13.373487168551002, totalpercent=-5.466540999057416,
> dollaramount=0.9453345900094259 where id=230827
> updateTime2=86
>
>
>
> On Monday, October 28, 2013 10:50:56 PM UTC-7, TrendTimer.com wrote:
>>
>>
>> Oh right, I forgot to mention that I was using Ormlite for the updates.
>> Maybe Ormlite is at fault? I'm pretty certain it's just doing an "update
>> xxx set a=1, b=2 where id=12345" kind of update.
>>
>>
>> On Monday, October 28, 2013 10:20:47 PM UTC-7, Kartweel wrote:
>>>
>>> Is that hibernate? I haven't used it for so long I forget the method
>>> names... You probably need to run it through a profiler (
>>> http://h2database.com/html/performance.html#built_in_profiler ) and see
>>> what it is doing. It could be doing optimistic locking checks or multiple
>>> queries or anything. At the minimum you need to find out what SQL it is
>>> executing so you can run explain on it (
>>> http://h2database.com/html/grammar.html#explain ) to see if it is using
>>> the index or doing a table scan or anything.
>>>
>>> Hope that helps a little bit :)
>>>
>>> Ryan
>>>
>>> On 29/10/2013 1:00 PM, TrendTimer.com wrote:
>>>
>>> adding CACHE_SIZE=32768 to the jdbc url helped speed up the updates to 6
>>> ms. So this is better, but still a lot slower that the inserts. I'm
>>> timing the inserts like this:
>>>
>>>
>>> long startTime=System.currentTimeMillis();
>>> try {
>>> getDao().createOrUpdate(combinedTestDay);
>>> } catch (SQLException e) {
>>> // TODO Auto-generated catch block
>>> e.printStackTrace();
>>> }
>>> long endTime=System.currentTimeMillis();
>>> long totalTime=endTime-startTime;
>>> System.out.println("createOrUpdateTime="+totalTime);
>>>
>>>
>>> and the output is always:
>>>
>>> createOrUpdateTime=0
>>>
>>>
>>> On Monday, October 28, 2013 9:32:23 PM UTC-7, TrendTimer.com wrote:
>>>>
>>>> When I use "createOfUpdate", I can insert 10000 rows in the table
>>>> almost immediately.
>>>>
>>>> getDao().createOrUpdate(combinedTestDay);
>>>>
>>>>
>>>> but then when I try to update these same items, it's taking around 70
>>>> ms each:
>>>>
>>>> getDao().update(saveObject);
>>>>
>>>> So for 10,000 updates that's 70,000ms or around 70 seconds.
>>>>
>>>> Can someone explain what might be happening? Does anyone know how to
>>>> work around this issue? I'd appreciate your help! thanks,
>>>>
>>>> Stephen Gower
>>>>
>>>>
>>>>
>>>>
>>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "H2 Database" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to [email protected].
>>> To post to this group, send email to [email protected].
>>> Visit this group at http://groups.google.com/group/h2-database.
>>> For more options, visit https://groups.google.com/groups/opt_out.
>>>
>>>
>>> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected] <javascript:>.
> To post to this group, send email to [email protected]<javascript:>
> .
> Visit this group at http://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
