Firebird: the way you compare dates highly influences fetch times
Posted by jpluimers on 2018/07/18
I was amazed the influence on how you compare dates would be a factor 1000 change in fetch times even though I wrote about date, time and timestamp conversions before in Source: Firebird – adjusting Delphi stored TDateTime (or OLE Automation date) fields that contain Dates, Times or DateTimes.
All queries were ran at 20170410.
Slow
select id, date '1899-12-30' + data.datadate as datestamp
from data
where 1=1
-- slow: prepared 0.066 sec; fetched 10.549 sec 250 rows
and date '1899-12-30' + data.datadate >= '2017-04-08' -- last 2 days
and date '1899-12-30' + data.datadate <= '2017-04-10' -- today
Fast
select id, date '1899-12-30' + data.datadate as datestamp
from data
where 1=1
-- fast: prepared 0.063 sec; fetched 0.009 sec 250 rows
and data.DATADATE >= date 'Now' - date '1899-12-30' - 2 -- last 2 days
and data.DATADATE <= date 'Now' - date '1899-12-30' -- today
Fast
select id, date '1899-12-30' + data.datadate as datestamp
from data
where 1=1
-- fast: prepared 0.070 sec; fetched 0.011 sec 250 rows
and data.DATADATE >= date '2017-04-10' - date '1899-12-30' - 2 -- last 2 days
and data.DATADATE <= date '2017-04-10' - date '1899-12-30' -- today
Fast
select id, date '1899-12-30' + data.datadate as datestamp
from data
where 1=1
-- fast: prepared 0.073 sec; fetched 0.009 sec 250 rows
and data.DATADATE >= date '2016-04-08' - date '1899-12-30' -- specific date range start
and data.DATADATE <= date '2016-04-10' - date '1899-12-30' -- specific date range end
The table looks like this:
CREATE TABLE "DATA" ( ID INTEGER NOT NULL, DATADATE DOUBLE PRECISION, DATATIME DOUBLE PRECISION ); CREATE ASC INDEX DATA_DATADATE ON "DATA" (DATADATE);
–jeroen
The Wiert Corner - irregular stream of stuff 
Leave a comment