| Subject | Re: [firebird-support] Re: Performance problem - input wanted |
|---|---|
| Author | |
| Post date | 2017-11-21T14:55:18Z |
Try changing to Afdeling_ID||'' (or Afdeling_ID+0 if it is a number) in your trigger. That should make RDB$FOREIGN105 useless for the query.
I did know about this.
But I can see now, that there are other querys around the program, which has the same WHERE clause, which causes the same PLAN.
And sine the foreign key only references 58 records (and will never reference more than a few hundred), and the table contains millions of records, I think its better to remove the foreign key.
I think I recall something about to few unique values will render the index / foreign key useless.
Though this kind of trigger that finds the MAX value in huge tables (although there's only 750 occurences of each VAREPLU_ID on average, I guess there are some values that are used a lot more) is a source for inserts being slow as well as potentially error prone (in case of concurrent inserts, the same values would be returned for both rows, which may or may not be what you want).
I know that now :)
But sinse this the code below this trigger is only used by a few customers, I will remove the trigger and do this elsewhere, so not all will be affected by this.