update performance in h2 so slow~~

[Caesar1985]

table structure as follows:

create table test
(
ID CHAR(32) NOT NULL,
YEAR CHAR(4),
MONTH CHAR(2),
COMPANY_ID CHAR(32),
COMPANY_CODE VARCHAR(16),
COMPANY_NAME VARCHAR(50),
EMPLOYEE_ID CHAR(32),
WAGE_DEFINE_NAME0 VARCHAR(50),
WAGE_DEFINE0 NUMERIC(8,2) DEFAULT 0,
WAGE_DEFINE_NAME1 VARCHAR(50),
WAGE_DEFINE1 NUMERIC(8,2) DEFAULT 0,
WAGE_DEFINE_NAME2 VARCHAR(50),
WAGE_DEFINE2 NUMERIC(8,2) DEFAULT 0,
WAGE_DEFINE_NAME3 VARCHAR(50),
WAGE_DEFINE3 NUMERIC(8,2) DEFAULT 0,
WAGE_DEFINE_NAME4 VARCHAR(50),
WAGE_DEFINE4 NUMERIC(8,2) DEFAULT 0,
WAGE_DEFINE_NAME5 VARCHAR(50),
WAGE_DEFINE5 NUMERIC(8,2) DEFAULT 0,
WAGE_DEFINE_NAME6 VARCHAR(50),
WAGE_DEFINE6 NUMERIC(8,2) DEFAULT 0,
WAGE_DEFINE_NAME7 VARCHAR(50),
WAGE_DEFINE7 NUMERIC(8,2) DEFAULT 0,
WAGE_DEFINE_NAME8 VARCHAR(50),
WAGE_DEFINE8 NUMERIC(8,2) DEFAULT 0,
WAGE_DEFINE_NAME9 VARCHAR(50),
WAGE_DEFINE9 NUMERIC(8,2) DEFAULT 0,
WAGE_DEFINET NUMERIC(8,2) DEFAULT 0,
FINANCE_BUDGET_SUB_ID CHAR(32),
FINANCE_BUDGET_SUB_CODE VARCHAR(12),
FINANCE_BUDGET_SUB_NAME VARCHAR(50),
ATTACH_FINANCE_DEP_ID CHAR(32),
ATTACH_FINANCE_DEP_CODE VARCHAR(16),
ATTACH_FINANCE_DEP_NAME VARCHAR(50),
OPERATOR CHAR(32),
SYSTEM_UPDATE_DATE VARCHAR(10),
REMARK VARCHAR(255),
USER_DEFINE_TXT0 VARCHAR(50),
USER_DEFINE_TXT1 VARCHAR(50),
USER_DEFINE_TXT2 VARCHAR(50),
USER_DEFINE_TXT3 VARCHAR(50),
...,
USER_DEFINE_TXT19 VARCHAR(50),
USER_DEFINE_NUM0 NUMERIC(8,2) DEFAULT 0,
USER_DEFINE_NUM1 NUMERIC(8,2) DEFAULT 0,
...,
USER_DEFINE_NUM89 NUMERIC(8,2) DEFAULT 0,
ID_CARD_TYPE VARCHAR(20),
CONSTRAINT PK_test PRIMARY KEY(ID)
)

I created an index for theses columns : MONTH, COMPANY_ID

And then, try to update data about 800 rows
the statement like this:

update test
set USER_DEFINE_NUM17 = USER_DEFINE_NUM0
where WAGE_MONTH = '08' and COMPANY_ID =
'8a95802321afc7850121afc794e5036b'

Surprisingly, It costs 1.7 seconds, but in hsqldb, same table and
update statement, it only costs 0.12 seconds

What reasons make h2 so slow during updating more than hundred rows,
but hsqldb is so fast??
Refer to the PERFORMANCE part of h2 documentation, update operation in
h2 should be more fast than hsqldb!!

[Sam Van Oort]

Hi,

Have you examined the performance tuning section? This is at:
http://www.h2database.com/html/performance.html#database_performance_tuning

See also advice here: http://www.h2database.com/html/faq.html#query_slow

The most likely cause is that indexes are not being used, because
ANALYZE has not been run and there are no statistics about column
selectivity. Run "ANALYZE" then use "EXPLAIN..." to check that
indexes are being used in the query plan.

If you search the groups, you'll find other, similar questions about
update performance -- the solutions there will probably be helpful to
you. In almost all cases the problem is something wrong in the
settings. The H2 engine on its own is very, very fast, and is
perfectly usable with thousands or millions of rows, with appropriate
settings and indices.

Regards,
Sam Van Oort
(Previously listed as Bob McGee for privacy reasons)

[Caesar1985]

Thanks for Sam Van Oort's response!
Following your recommendation, I check the usage of indexes. I use
"analyze" statement to refresh the state of indexes , and then execute
explain for my update statement, and the plain is like this:

----------------------------------------------------------------------------
UPDATE PUBLIC.TEST
/* PUBLIC.I_TEST_01: MONTH = '08' AND COMPANY_ID =
'8a95802321afc7850121afc794e5036b' */
SET USER_DEFINE_NUM17 = USER_DEFINE_NUM0
WHERE (P.MONTH = '08') AND (P.COMPANY_ID =
'8a95802321afc7850121afc794e5036b')
----------------------------------------------------------------------------

I think the correct index is used. Updating about 800 rows costs
nearly 1.8 seconds. For the customer's need, I have to be back to
hsqldb before I found other clues to solve this problem.

Regards,
Kai Guo

On 8月19日, 下午3时02分, Sam Van Oort <buckyba...@gmail.com> wrote:
> Hi,
>

> Have you examined the performance tuning section?  This is at:http://www.h2database.com/html/performance.html#database_performance_...

[Thomas Mueller]

Hi,

According to my tests, H2 is slower in this case, specially if you
have many indexes. However, not that much slower. Do you use HSQLDB
using CREATE TABLE or CREATE CACHED TABLE? By default HSQLDB keeps all
rows in memory, which is faster, but can result in memory problems. H2
also supports 'memory tables' (CREATE MEMORY TABLE).

My test case:

drop table test;
create table test(id int primary key, name varchar(1000), x int);
@LOOP 10000 insert into test values(?, space(1000), 0);
@LOOP 10 update test set x = 1 where id < 800;
-- H2: 2281 ms
-- HSQLDB: 404 ms

Regards,
Thomas

[Caesar1985]

In hsqldb, in my app the size of db file is nearly 36Mb, directly
convert it into h2 db, the size of data and index file is 56Mb and
37Mb separately.

I use jdbc to execute the create and insert sql in h2, and also attemp
to use 'create memory table ...' statement, forcing the h2 load data
into memory, but after convertion, I use jdbc or other tool to connect
the h2 db file, waiting a long time and then get an out of memory
exception, I don't kown why, but obviously memory mode in this
workround is useless for me.

Refer to your suggestion, I compared memory mode and cached mode in
hsqldb, using the table structure and update statement which is
mentioned before.

Loop CACHED MEMORY
1 0.265 0.62
2 0.234 0.78
3 0.172 0.94
4 0.187 0.31
5 0.93 0.47
6 0.172 0.47
7 0.94 0.46
8 0.156 0.31
9 0.11 0.47
10 0.11 0.109
SUM 3.276 4.939
AVG 0.3276 0.4939

The cached mode is faster than memory mode~~ but the db file in cached
mode is huge for my app, normally twice the size of memory mode.

So far the size of the biggest db file in my app is about 40Mb, and no
memory problem occurred. But your recommendation is worthy, I'll
carefully testify the status of memory usage.

I forgot to introduce my tool version
hsqldb 1.8.0.10
h2 1.1.116
Execute Query 3.1.5


Regards,
Kai Guo


On Aug 26, 6:59 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> According to my tests, H2 is slower in this case, specially if you
> have many indexes. However, not that much slower. Do you use HSQLDB
> using CREATE TABLE or CREATE CACHED TABLE? By default HSQLDB keeps all
> rows in memory, which is faster, but can result in memory problems. H2
> also supports 'memory tables' (CREATE MEMORY TABLE).
>
> My test case:
>
> drop table test;
> create table test(id int primary key, name varchar(1000), x int);
> @LOOP 10000 insert into test values(?, space(1000), 0);
> @LOOP 10 update test set x = 1 where id < 800;
> -- H2: 2281 ms
> -- HSQLDB: 404 ms
>
> Regards,
> Thomas
>
>
>
> On Wed, Aug 19, 2009 at 12:52 PM, Caesar1985<kaigu...@gmail.com> wrote:
>
> > Thanks for Sam Van Oort's response!
> > Following your recommendation, I check the usage of indexes. I use
> > "analyze" statement to refresh the state of indexes , and then execute
> > explain for my update statement, and the plain is like this:
>

> > ---------------------------------------------------------------------------­-

> > UPDATE PUBLIC.TEST
> > /* PUBLIC.I_TEST_01: MONTH = '08' AND COMPANY_ID =
> > '8a95802321afc7850121afc794e5036b' */
> > SET USER_DEFINE_NUM17 = USER_DEFINE_NUM0
> > WHERE (P.MONTH = '08') AND (P.COMPANY_ID =
> > '8a95802321afc7850121afc794e5036b')

> > ---------------------------------------------------------------------------­-

> >> > h2  should be more fast than hsqldb!!- Hide quoted text -
>
> - Show quoted text -

[Chris Schanck]

My broken record answer for H2 performance problems: SET CACHE=<more memory>

Chris

--
C. Schanck