Subject Re: [firebird-support] Very slow select Index with null date
Author Svein Erling Tysvær
Post date 2018-10-10T14:21:46Z
Try comparing

SELECT count(*)
FROM DM251 A
WHERE    A.DT_INDEX IS NULL  

to

SELECT count(*)
FROM DM251 A
WHERE    A.DT_INDEX IS NOT NULL  

And what are the PLANs for these two selects (including name of field(s) if not intuitive)? My guess regarding your initial timing, is that only the first few rows are returned in the fast query (and possibly that there's no active index for the DT_INDEX field).

Set

Den tir. 9. okt. 2018 kl. 16:46 skrev Hugo Eyng hugoeyng@... [firebird-support] <firebird-support@yahoogroups.com>:


Hello.

I created an index on a table where de column is datetime type. The problem seems to be related to the NULL value.

This SQL is very SLOW (about 7 seconds for a table containing 360.000 records). Returns around 400 records
  
SELECT
A.cd_xxx, A.dt_1, A.dt_2, A.dt_3, A.dt_4, A.dt_5, A.dt_index​
FROM DM251 A​
WHERE    A.DT_INDEX IS NULL

This SQL is very FAST (less than 0.5 seconds for a table containing 360.000 records). Returns around 359.600 records

SELECT
A.cd_xxx, A.dt_1, A.dt_2, A.dt_3, A.dt_4, A.dt_5, A.dt_index​
FROM DM251 A​
WHERE    A.DT_INDEX IS NOT NULL



Atenciosamente,

+ + Hugo Eyng + +