The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,402 other followers

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: