Segmentation Fault Kore Dumped

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