Library

Using prefix date to speed up queries with DATE columns

Firebird Friday Joke #5 (from https://t.me/firebirdsql)

Do you know that Firebird supports special prefix for symbolic date parameters?

If for some reason, you are specifying a condition for the column of type DATE, and comparing it with a string constant, you can specify prefix date before the parameter, to inform Firebird that it should consider it as a date.

As a result, the query can become up to 25% faster!

See the example below:

SQL> select count(*) from SYNC_DATA where DATE_IN > '01-AUG-2019';

                COUNT
=====================
              3846157

Current memory = 17092054352
Delta memory = 0
Max memory = 17115757856
Elapsed time = 6.339 sec
Buffers = 512000
Reads = 0
Writes = 0
Fetches = 3900383
SQL> select count(*) from SYNC_DATA where DATE_IN > date '01-AUG-2019';

                COUNT
=====================
              3846157

Current memory = 17092054368
Delta memory = 16
Max memory = 17115757856
Elapsed time = 4.933 sec
Buffers = 512000
Reads = 0
Writes = 0
Fetches = 3900383
SQL>

Of course, better always use prepared statements!

For new Firebird Friday Jokes and Firebird-related news, subscribe to FirebirdSQL telegram https://t.me/firebirdsql