| Subject | Re: [firebird-support] What is the best way to re-write this Stored Procedure that seems to be SLOW processing? |
|---|---|
| Author | setysvar |
| Post date | 2015-08-15T13:42:23Z |
Den 15.08.2015 06:14, skrev 'Walter R. Ojeda Valiente'
sistemas2000profesional@... [firebird-support]:
Generally, NATURAL is the quickest way to go through all records in a
table, whereas indexes are quicker if you're only considering a few of
them. As I expected, the threshold when an index becomes sensible for a
particular query, is below 44% (I think I've said before that I don't
know where the threshold is, just guessed somewhere between 5 and 20 %
(it's purely a wild guess, I know nothing about the Fb code and have not
done any measurements)). However, going from 2 hours 45 minutes to 12
minutes is simply too much of an improvement if the only change in the
plan is whether this particular index is used or not. Although Fb 2.5 is
considerably better than 1.5, Fb 1.5 wasn't that bad in utilizing
indexes. Did the plan change in some other way, or was the only
difference that this index was replaced by natural?
I cannot explain your improvement. Fb 1.5 (and before) has problems with
many duplicates in an index, but that problem manifest itself with
update and delete, not select. That particular problem is also simple to
fix, just add your primary key field to the end of the index. Another
problem with Fb 1.5 is that it sometimes have problems to choose which
index to use if you have to identical indexes, but you haven't indicated
that you have that.
Set
sistemas2000profesional@... [firebird-support]:
> Hello SetSure Walter.
>
> Why writing: PP.STATUS_CODE || '' will improve the SELECT, can you
> explain me?
> As far I know, adding '' is for use NATURAL in the PLAN, why the use
> of NATURAL will do a better response time?
> Thanks in advance.
>
> Greetings.
> Walter
Generally, NATURAL is the quickest way to go through all records in a
table, whereas indexes are quicker if you're only considering a few of
them. As I expected, the threshold when an index becomes sensible for a
particular query, is below 44% (I think I've said before that I don't
know where the threshold is, just guessed somewhere between 5 and 20 %
(it's purely a wild guess, I know nothing about the Fb code and have not
done any measurements)). However, going from 2 hours 45 minutes to 12
minutes is simply too much of an improvement if the only change in the
plan is whether this particular index is used or not. Although Fb 2.5 is
considerably better than 1.5, Fb 1.5 wasn't that bad in utilizing
indexes. Did the plan change in some other way, or was the only
difference that this index was replaced by natural?
I cannot explain your improvement. Fb 1.5 (and before) has problems with
many duplicates in an index, but that problem manifest itself with
update and delete, not select. That particular problem is also simple to
fix, just add your primary key field to the end of the index. Another
problem with Fb 1.5 is that it sometimes have problems to choose which
index to use if you have to identical indexes, but you haven't indicated
that you have that.
Set