Subject Re: [firebird-support] "EXTRACT (YEAR FROM DT.DATA) <= 2016" or "DT.DATA <= '12/31/2016'"
Author LtColRDSChauhan

On Sat, Dec 10, 2016 at 12:37 AM, Louis Kleiman lkleiman@... [firebird-support] <> wrote:

You can create an expression index on EXTRACT (YEAR FROM DT.DATA) to get performance improvements in your first expression, but an index on plain ol' DT.DATA would be more flexible -- it would provide assistance in more cases than the expression index. The plain ol' column index would speed up the DT.DATA <= '12/31/2016', but not the EXTRACT... expression.

As always in our business, tradeoffs...

On Fri, Dec 9, 2016 at 12:12 PM Luigi Siciliano luigisic@... [firebird-support] <firebird-support@yahoogroups. com> wrote:

Il 09/12/2016 15.47, Mark Rotteveel mark@... [firebird-support] ha scritto:


Could you please put all pertinent information of your question in the
body, and not only in the subject. It makes questions a lot easier to
read (especially on mobile devices).

I'm sorry, I rewrite the question:

  what is more performant: "EXTRACT (YEAR FROM DT.DATA) <= 2016" or "DT.DATA <= '12/31/2016'"?

DT.DATA <= '31.12.2016' /* date format */
could be ignoring the hours part ie from '31.12.2016, 00:00:00.001' to '31.12.2016, 23:59:59.999'
better use
DT.DATA < '1.1.2017'  

I would prepare myself for a table that grows rapidly.

Is the same in FB 2.5.6 or 3.0.1?


Luigi Siciliano

  Lt Col (Retd) Rajiv D.S. Chauhan