2

In the article 45 Ways To Speed Up Firebird Database, it states that

  1. Use derived tables to optimize SELECT with ORDER BY/GROUP BY

Another way to optimize SQL query with sorting is to use derived tables to avoid unnecessary sort operations. Instead of

SELECT FIELD_KEY, FIELD1, FIELD2, ... FIELD_N
FROM T
ORDER BY FIELD2

use the following modification:

SELECT T.FIELD_KEY, T.FIELD1, T.FIELD2, ... T.FIELD_N FROM
    (SELECT FIELD_KEY FROM T ORDER BY FIELD2) T2
JOIN T ON T.FIELD_KEY = T2.FIELD_KEY

I assume T.FIELD_KEY means a primary key column.

In other words, it shunts the sorting to a subquery, and then 'self-joins' to get the rest of the necessary columns.

As I understand it, this will let Firebird do the sorting on a narrower resultset which would use less memory and be more cache-friendly, but surely it shouldn't give that much a boost? Especially when it needs to fetch the other columns afterwards, potentially in a random I/O pattern too.

On the other hand, this reeks of undefined behaviour in that there's no guarantee (in standard SQL) that the engine would retain the order after the join -- it could just join the other way, wouldn't it?

Are my guesses correct? Am I missing something?

  • I'd agree with the undefined behaviour concern; this may well work with Firebird (given #29 in that list) but that's no guarantee it'll either work with other databases or for that matter with future versions of Firebird. Still, at least it's way down the list (and most of the rest look pretty sensible, although there's a few "optimizer workaround" entries) – Dave Jones May 25 '16 at 8:08
  • Hm, I now realise that item 30 might be a continuation of item 29. It's just oddly phrased: "another way" makes it sound standalone. – oals May 27 '16 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.