Simple query optimization

[www.ePortfel.com]

Hi,

In my personal finance management application I have TRANSAKCJA table
that contains transactions.
I have one SQL query that makes the program to work too slowly and I
have no idea how to optimize it.

The goal of the query is to get the newest row (the row of maximum
DATA column value) but not newer than given DATA parameter and the row
must be related to specific account (specific int as foreign key). So
I get all rows of given foreign key and not newer than given data and
sort it from newest to get the first row:

SELECT LIMIT 0 1 'eportfel.db.Transakcja' AS NUCLEUS_TYPE,THIS."DATA"
AS NUCORDER0,THIS.ID AS NUCORDER1
FROM TRANSAKCJA THIS
WHERE ((84 = THIS.KONTO_Z_ID_KONTO_OID OR 84 =
THIS.KONTO_NA_ID_KONTO_OID))
AND THIS."DATA" <= TIMESTAMP '2012-02-09 17:53:17.019'
ORDER BY NUCORDER0 DESC,NUCORDER1 DESC;

It works great but too slow.
For example above, the result set after filtering has two rows taken
from the table containing 17000 rows.
It takes about 200-300 ms to execute. I created many indices in the
table but it does not help.
What is interesting is that removing ORDER BY clause makes execution
about 3 times faster, even when it seems it has only 2 rows to sort,
hm...

Any ideas how to make it work really fast?

[Thomas Mueller]

Hi,

I suggest to read the documentation, specially
http://h2database.com/html/performance.html

If you can't figure it out, you might get more help using StackOverflow.

Regards,
Thomas