Have you got an index on the ID column?, it doesn't appear to be using
an index. Usually you would have ID as the primary key which would
create an index...
On 29/10/2013 3:17 PM, TrendTimer.com wrote:
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
<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
<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 h2-database...@googlegroups.com.
To post to this group, send email to
h2-da...@googlegroups.com.
Visit this group at
http://groups.google.com/group/h2-database
<http://groups.google.com/group/h2-database>.
For more options, visit
https://groups.google.com/groups/opt_out
<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 h2-database...@googlegroups.com <javascript:>.
To post to this group, send email to h2-da...@googlegroups.com
<javascript:>.
Visit this group at http://groups.google.com/group/h2-database
<http://groups.google.com/group/h2-database>.
For more options, visit https://groups.google.com/groups/opt_out
<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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
