Fetch most recent value – sql execution time too long

I have a Firebird database with a table with two columns: Date and value.

I want to get the most recent value. Problem is, that this table can have easily over 200k rows. My select query takes more than 400ms, which is just to long for my application. Is there any way, I can speed this up?

I can’t change the database in any way. I cannot use any window function introduced in Firebird 3.0.

Here is my query:

SELECT  REG_DATE_TIME,REG_VAL FROM TAB_REG_VAL  
WHERE REG_DATE_TIME = (SELECT MAX(REG_DATE_TIME)  FROM TAB_REG_VAL);

I also tried select first .. order by, but the execution time was similar. I am using C# ado.net connected layer, if that’s important.

Answer

You need to create a descending index for the column REG_DATE_TIME:

create descending index idx_tab_reg_val_reg_date_time on TAB_REG_VAL(REG_DATE_TIME);

You can then use

SELECT FIRST 1 REG_DATE_TIME,REG_VAL 
FROM TAB_REG_VAL
ORDER BY REG_DATE_TIME DESC

Without the index, Firebird will need to materialize and sort the entire result set before it can return you that first row. This is inefficient (and can get even worse if the result set is larger than the sort memory, in which case Firebird will sort in a temporary file on disk).

With the index, Firebird just needs to access a few pages in the index and one or more records in the table to locate the first record that is visible to your transaction.

Note: Firebird indexes can – currently – only be used for sorting in a single direction, which means that depending on your access needs, you may need to create an index in ascending direction as well.