Segmentation Fault Kore Dumped

If rows are deleted from the Apache Derby Database, the table doesn't get compressed automagically. You need to execute the following command with derby utility "ij".

call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SCHEMA-NAME', 'TABLE-NAME', 1)

The third option is something called "sequential" which takes less memory and space, as you have guessed it, more time. But, really it is not that slow. It reduced the 1.3 G table to 600M in less than a minute. More gyaan on the man page.

Labels: cleanup, compress, derby

Hit a performance problem with Apache derby, which turned out not to be Derby specific problem anyway. The tip that the day is avoiding queries which incur entire table scan:


SELECT DISTINCT nonIndexedCol FROM HugeTable;

SELECT * FROM HugeTable ORDER BY nonIndexedColumn;

For Derby, the table is called "huge" if it has more than few thousand rows. (It's an embedded database.)

To debug derby performance, set the properties given below and watch derby.log

System.setProperty("derby.language.logQueryPlan", "true");
System.setProperty("derby.language.logStatementText", "true");

Also, realized that

• Performance problems are more like 50-1 rather than 80-20. That is, 1% effort gives 50% result.
• Logging messages and measuring delay at macro level is easier than running a profile.

Labels: db, derby