Hello Set I am not who originally post the question, just a curious reader.
And I use Firebird 2.5.4 only, I don't like to have several versions because they can have different problems. Just one version for all clients and all servers and all databases, it is the better, according to my point of view. Of course that NATURAL is the faster way when you want read all the rows of a table, but going from 2 hours and 45 minutes to 12 minutes is a extremely great improvement and I was very interested for understand the reason. Greetings. Walter. On Sat, Aug 15, 2015 at 9:42 AM, setysvar [email protected] [firebird-support] <[email protected]> wrote: > > > Den 15.08.2015 06:14, skrev 'Walter R. Ojeda Valiente' > [email protected] [firebird-support]: > > Hello Set > > > > 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 > Sure 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 > >
