I think I just figured it out.. For some reason there is no index for the
primary key! (id) for this table. I'll add it and check it again
(tomorrow.. time for bed).. Thanks for your help Kartweel
On Tuesday, October 29, 2013 12:17:46 AM UTC-7, 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 ) 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].
>> 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].
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.
