Slow select for a large table

[poker...@yahoo.de]

My simplified select statement: select count(*),
SUM(CONVERT(aFlag,INT)) from Test1 where fk_test2=999
Test1 has about 30 columns
fk_test2 is a foreign key ( index used)

Table Test1 has about 6,000,000 rows.
count(*) = 70,000

The first time i execute the statement it takes 90000ms
The second time 9000ms, and then 900ms

Is it possible to reduce the first execution time?
How does the cache work? I have not found a detailed description.

Regards

[Thomas Mueller]

Hi,

> ( index used)

How did you verify the index is used?

Could you post the result of "EXPLAIN ANALYZE SELECT ..."?

Could you also post the CREATE TABLE statement for this table? If the
table has large column (large VARCHAR columns) then this might be the
reason (use BLOB instead).

See also http://h2database.com/html/performance.html

Regards,
Thomas

[poker...@yahoo.de]

Hi,

EXPLAIN ANALYZE:

SELECT COUNT(*), SUM(CONVERT(AFLAG,INTEGER)) AS CNTAFLAG
FROM Test1 THIS_ /* FKC3BE9D399BE4559C_INDEX_A: fk_test2 = 22741 */ /*
scanCount: 76246 */
WHERE THIS_.fk_test2 = 22741
/*
total: 76433
Test1.FKC3BE9D399BE4559C_INDEX_A read: 559 (0%)
Test1.Test1_DATA read: 75874 (99%)
*/

Test1 contains only one VARCHAR(255) column, all the other columns are
either INTEGER(10) OR BOOLEAN(1)

Regards,
Peter

On Jun 22, 8:45 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Thomas Mueller]

Hi,

The problem could be fragmentation in the database file. Could you try
SHUTDOWN DEFRAG? See also
http://h2database.com/html/grammar.html#shutdown

Regards,
Thomas

[lumen]

Thank you!

After SHUTDOWN DEFRAG the first execution of the statement takes
9000ms.

Regards, Peter

[priyanka]

Hi,

I have similar problem when Iam inserting 1 million database it is taking 25 secs...
The same database when iam retrieving it takes 100sec...

Please can you help me how to decrease the time while Retrieving.

Thanks in advance ..