SQL / Derby: Get the last line in the array. Poor performance with MAX function

advertisements

I need to know the last entry in a derby database with a few million entries. Using the MAX() function I get very poor performance. I also tried scrollable resultSets and jumping to the last entry but this is even worse.

Any easy simple performant way to get the last line without iterating over all entries?

SQL: select max(IDDATALINE) from DATADOUBLE_2 where DATADOUBLE_2.DATATYPE = 19 Table: IDDATALINE [BIGINT] | DATATYPE[INTEGER] | DATA [DOUBLE]

I dont know if any indexes are defined, i'm working with source code I took over. I'll see if I finde something, if I don't find anything where/how do I add an index?


This is your query:

select max(IDDATALINE)
from DATADOUBLE_2
where DATADOUBLE_2.DATATYPE = 19;

You should be able to improve performance by creating an index. I would recommend:

create index idx_DATADOUBLE2_DATATYPE_IDDATALINE on DATADOUBLE_2(DATATYPE, IDDATALINE)

Note this is a composite index using two columns in that particular order.