获取最新值-SQL执行时间太长 [英] Fetch most recent value - sql execution time too long

问题描述

我有一个Firebird数据库，其中的表具有两列:日期和值.

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

我想获取最新值.问题是，该表可以轻松拥有超过20万行.我的选择查询花费了超过400毫秒的时间，这对我的应用程序来说太长了.有什么办法可以加快速度吗?

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?

我无法以任何方式更改数据库.我不能使用Firebird 3.0中引入的任何窗口功能.

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

这是我的查询:

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

我也尝试过select first .. order by，但是执行时间相似. 如果重要的话，我正在使用C#ado.net连接层.

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.

推荐答案

您需要创建降序

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);

然后您可以使用

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

没有索引，Firebird将需要具体化并排序整个结果集，然后才能返回第一行.这样效率低下(如果结果集大于排序内存，则可能更糟，在这种情况下，Firebird会在磁盘上的临时文件中排序).

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).

有了索引，Firebird只需访问索引中的几页以及表中的一个或多个记录，即可找到对您的交易可见的第一条记录.

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.

注意:Firebird索引当前只能用于单个方向的排序，这意味着，根据您的访问需要，您可能还需要沿升序创建索引.

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.

这篇关于获取最新值-SQL执行时间太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！