How to set timeout in Firebird with HeidiSQL?

Question

I have Firebird 4.0, querying from HeidiSQL. After just two nested joins, HeidiSQL returns:

SQL Error (335544794): [FireDAC][Phys][FB]operation was cancelled Statement level timeout expired.

I believe the query would've run just over a second. After googling a little bit, I found out to change the "statement timeout" with SET STATEMENT TIMEOUT 1000 SECOND;. For some reason, the message still appears, as if Firebird ignores my statement.

Full query in HeidiSQL:

SET STATEMENT TIMEOUT 1000 SECOND;

SELECT FIRST 100 * FROM STAGE_TABLE
LEFT OUTER JOIN DIM_TABLE ON STAGE_TABLE.ID = DIM_TABLE.ID
LEFT OUTER JOIN FACT_TABLE ON STAGE_TABLE.ID = FACT_TABLE.ID;

Select plus only the first join takes about half a second, adding the second join runs into expired statement. It seems like HeidiSQL or Firebird just ignores my first SQL command. How can I fix that?

Answer 1

I have been able to reproduce this. The problem is that HeidiSQL has a (default) query timeout setting of 30 (no unit), and it seems this gets passed on execute of a statement without taking into account that Firebird expects milliseconds.

In other words, Firebird receives an instruction to execute the statement with a timeout of 30 milliseconds, which is pretty short. This then results in the "operation was cancelled Statement level timeout expired." error.

That is because a non-zero timeout passed on execute takes precedence over the session-level statement timeout you set with SET STATEMENT TIMEOUT (though if it is higher than a non-zero database-level statement timeout, it will use the database-level statement timeout).

To fix this, in the session manager of HeidiSQL, select your Firebird server, go to the tab "Advanced" and in "Query timeout" enter 0 (for no statement-level timeout), or enter the desired timeout value (taking into account that it is in milliseconds).