| Subject | Re: [firebird-support] Very slow select Index with null date |
|---|---|
| Author | liviuslivius |
| Post date | 2018-10-09T15:36:35Z |
Hi,
more detais needed.
Do all with same connection
1. Connect to db
2. Run query without where clause
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
3. Run query
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 NULL4. Run query
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 NULL5. Disconnect from db
What are time stats of point 2,3,4?
Regards,
Karol Bieniaszewski
-------- Oryginalna wiadomość --------
Od: "Hugo Eyng hugoeyng@... [firebird-support]" <firebird-support@yahoogroups.com>
Data: 09.10.2018 16:46 (GMT+01:00)
Do: firebird-support@yahoogroups.com
Temat: [firebird-support] Very slow select Index with null date
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 + +