Prevent the user from issuing expensive queries

Some applications have complete control over the queries that they issue; the queries are built into the applications. Other applications allow users to construct queries by filling in fields on a form.

Any time you let users construct ad-hoc queries, you risk the possibility that the query a user constructs will be one like the following:

SELECT * FROM ExtremelyHugeTable
ORDER BY unIndexedColumn

This statement has no WHERE clause. It will require a full table scan. To make matters worse, Derby will then have to order the data. Most likely, the user does not want to browse through all 100,000 rows, and does not care whether the rows are all in order.

Do everything you can to avoid table scans and sorting of large results (such as table scans).

You can do the following to prevent such runaway queries:

Related concepts
Always create indexes