Very slow delete data in H2

[Hom L]

Hi,

I have created a H2 table with an index of several fields and loaded 2 million records (the data file grows to 600 MB). When I tried to delete those data with the key, it takes about 5 minutes to do so.

I have already set the LOCK MODE as 0. Is there any way to improve the performance.

Regards,

[Hom L]

+Additional Info

It's running in file mode and embedded mode.

Regards,

[Noel Grandin]

are you using an index?

and what does the create table and delete statement look like?

> --
> You received this message because you are subscribed to the Google
> Groups "H2 Database" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/h2-database/-/dovzhjPIkRAJ.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.

[Hom L]

The table created has two date fields , 11 varchar(255) and 9 double fields. The index contains the date field K1 and one varchar field K2.

the index is like (K1,K2)

the delete statement is like : delete from TableA where K1='2012-01-01' and K2='ABC'

The only jdbc parameter used is LOCK_SCHEMA=0.

now that I think about it. it might be that each line of table might contain too much space( those varchar(255) + doubles). I wonder if any other parameters that can help for such case.

[Thomas Mueller]

Hi,

Would it be possible to create an index as follows:

create index idx_TableA_k1_k2 on TableA(k1, k2)

and then delete 1000 rows at a time, until no more rows are found, using:

delete from TableA where K1='2012-01-01' and K2='ABC' limit 1000;
delete from TableA where K1='2012-01-01' and K2='ABC' limit 1000;
delete from TableA where K1='2012-01-01' and K2='ABC' limit 1000;
...

(until the updateCount is 0).

Regards,
Thomas

> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To view this discussion on the web visit

> https://groups.google.com/d/msg/h2-database/-/e0ZR351D3_oJ.

[Hom L]

That sounds like a good idea. I will try it out when I get back to work. Thanks for the advice.

Regards,

[Mark Addleman]

fyi - I take a very similar approach to deleting old rows in a very high insert rate application.  It's a very effective strategy

[Hom L]

That works much better. Now it only takes about 25 seconds to delete these 2 million records.

Thanks for the advice.