INTERBASE strange performance problem

[mvanrijnen 123]

Queries are shortened from the real query's.

(the problem is in the underlined lines of code)

 

SELECT DISTINCT slm.sma2_log_merged_idno
 FROM sma2_log_merged slm
INNER JOIN sma2_message sm ON (sm.sma2_message_idno = slm.sma2_message_idno)
                          AND ((sm.ignore=FALSE) OR (slm.is_alarm=TRUE))
INNER JOIN sma2_plant sp ON (sp.sma2_plant_idno = slm.sma2_plant_idno)
                        AND ((sp.ignore_untill IS NULL) OR (sp.ignore_untill<CURRENT_DATE))
                       --AND (sp.deleted_for_me=FALSE)
                       AND (NOT (sp.deleted_for_me=TRUE))
WHERE ((slm.forget_until IS NULL) OR (slm.forget_until<CURRENT_DATE))
      AND (slm.deleted=FALSE)
      AND (slm.afgehandeld=FALSE)   
      AND (((slm.datetime_end>= '2021-12-03') AND (slm.leeftijd_in_dagen>=2)) OR (slm.is_alarm=TRUE))
  

Prepared in: 0.825 sec

Processes in: 0.0190 sec 

 

SELECT DISTINCT slm.sma2_log_merged_idno
 FROM sma2_log_merged slm
INNER JOIN sma2_message sm ON (sm.sma2_message_idno = slm.sma2_message_idno)
                          AND ((sm.ignore=FALSE) OR (slm.is_alarm=TRUE))
INNER JOIN sma2_plant sp ON (sp.sma2_plant_idno = slm.sma2_plant_idno)
                        AND ((sp.ignore_untill IS NULL) OR (sp.ignore_untill<CURRENT_DATE))
                       AND (sp.deleted_for_me=FALSE)
                       --AND (NOT (sp.deleted_for_me=TRUE))
WHERE ((slm.forget_until IS NULL) OR (slm.forget_until<CURRENT_DATE))
      AND (slm.deleted=FALSE)
      AND (slm.afgehandeld=FALSE)   
      AND (((slm.datetime_end>= '2021-12-03') AND (slm.leeftijd_in_dagen>=2)) OR (slm.is_alarm=TRUE))
  

Prepared in: 0.823 sec

Processed in: 21.851 sec ??

 

So here's a big differnce between

"AND (sp.deleted_for_me=FALSE)"  and "AND (NOT (sp.deleted_for_me=TRUE))"

 

deleted_for_me is a not null field so always contains a TRUE or a FALSE value.

 

(performed in Database WorkBench 4)

 

 

Edited December 8, 2021 by mvanrijnen

[Guest]

I guess there's an index that cannot be used.

You should check the "performance" stats and compare the two.

You will probably see what reads are done indexed/non indexed using the index in the first and not in the second.

 

Do you have any "expression indices" on that table? Check that too.

It is not impossible that something forces IB to do a complete scan using the NOT.

Performance stats will tell you.

[Serge_G 87]

SELECT DISTINCT slm.sma2_log_merged_idno
 FROM sma2_log_merged slm
INNER JOIN sma2_message sm ON sm.sma2_message_idno = slm.sma2_message_idno
                         
INNER JOIN sma2_plant sp ON sp.sma2_plant_idno = slm.sma2_plant_idno
                        
WHERE ((slm.forget_until IS NULL) OR (slm.forget_until<CURRENT_DATE))
      AND (slm.deleted=FALSE)
      AND (slm.afgehandeld=FALSE)   
      AND (((slm.datetime_end>= '2021-12-03') AND (slm.leeftijd_in_dagen>=2)) OR (slm.is_alarm=TRUE))
      AND ((sm.ignore=FALSE) OR (slm.is_alarm=TRUE))
  AND ((sp.ignore_untill IS NULL) OR (sp.ignore_untill<CURRENT_DATE))
                       --AND (sp.deleted_for_me=FALSE)
                       AND (NOT (sp.deleted_for_me=TRUE))

Try this one too, I am not certain that using where condition in jointures is a good idea

[mvanrijnen 123]

1 hour ago, Serge_G said:

Try this one too, I am not certain that using where condition in jointures is a good idea

I remember reading somewhere (not sure where 🙂 ) the opposite, some documentatin about this?

 

Have to look this up, your variant with the original "AND (sp.deleted_for_me=FALSE)" is faster then my solution.

man man,  some work to do now 🙂

 

[edit]

Seems we have some other problems here, the old query now performs faster also. 

(not as fast as it should)

 

Edited December 9, 2021 by mvanrijnen

[Guest]

@mvanrijnen, i'm an FB guy, but IB also has an "optimizer". The "optimizer" will select indices.

It should be documented somewhere. Also IB i guess also uses selectivity of indices and those may have to be recalculated some times.

If the DB is not too big, make a backup and restore to another db file. Compare the two.

[mvanrijnen 123]

3 hours ago, Dany Marmur said:

If the DB is not too big, make a backup and restore to another db file. Compare the two.

Yes i know, we do that every month here.

But somehow the query which ran yesterday in 1.21 (on testdatabase), ran this morning in 0.37, so have to figure out what that is.

The test DB is a database which get restored everynight from a backup from the productiondb.

So it could be that some process is messing up the index stats  or so, have some more testing todo before finding out.

 

 

Edited December 9, 2021 by mvanrijnen

[Serge_G 87]

24 minutes ago, mvanrijnen said:

The test DB is a database which get restored everynight from a backup from the productiondb.

Ok so, if backup is like the Firebird one, emptying garbage, I guess you have some problems with transactions 

[Guest]

On to index selectivity, optimizer details and expression indices!

Do not forget "monitoring".

[Sriram 4]

Can you please state the InterBase version and the PLAN output? You may want to use 'isql' command-line (and SET PLAN ON) before running the SQL. The PLAN output will let you know if an index on "sp.deleted_for_me" column is being used for the SQL statements.

 

Just FYI: InterBase 2020 version converts predicates with NOT condition to their equivalent condition to not use NOT, where possible if an index could be matched to optimize the retrieval. Earlier versions of InterBase did not do this. 

 

Users have in the past used the "NOT a = <boolean value>" as a way to instruct the optimizer to not use a really bad index (bad selectivity index) on column "a".  You may be using an older version of InterBase that allows you to discard the use of such an index for this condition with the NOT condition.

 

Caution: if you'd like to hint the optimizer to not use such a bad index in InterBase 2020 with the NOT condition, consider having a shadow column (deleted_for_me_copy COMPUTED BY deleted_for_me) and no index on deleted_for_me_copy. This will allow you to have the original index for good value matches (deleted_for_me = TRUE), and use the condition "deleted_for_me_copy = FALSE" for the large number of duplicate FALSE value records.

 

Alternatively, if you do not want to create a shadow copy of the column, you can just provide the well optimized PLAN with your query; the one where the index is not used for the condition

(NOT (sp.deleted_for_me=TRUE))