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.
On Thu, Aug 13, 2015 at 4:49 PM, setysvar setys...@gmail.com
[firebird-support] <firebird-support@yahoogroups.com> wrote:
>
>
> >>Two more questions, Karol:
>
> Sorry, I meant to write Mike.
>
> >>How large percentage of the records in PER_PHONE have status_code G or V?
> >>What's the definition of REFPERSON15?
>
> >The PER_PHONE table currently contain 579,873 rows
> >91455 or 15.771% is ‘G’
> >166075 or 28.639% is ‘V’
> >For a total of 44.41%
>
> Then I'd expect a small improvement if you generally changed to
>
> AND PP.STATUS_CODE||'' IN ('G','V')
> and
> ORDER BY PP.STATUS_CODE||''
>
> I don't know whether the improvement would be from 2:45 hours to 2:40
> hours or 1:45 hours, but it would still be slowish and I'd be very
> surprised if it was enough to return what you want in less than 1 hour. I
> say slowish rather than slow, because executing a stored procedure 42000
> times in 2:45 hours would mean that the stored procedure is executed more
> than 4 times per second.
>
> I thought about rewriting your stored procedure to a view, but failed (I
> think that would require windowing functions, which aren't available in
> Firebird 2.5). So, if this is something reasonably frequently executed, I
> would have considered rewriting it to be a new table maintained through one
> or more triggers on the PER_PHONE tables (alternatively, you could have a
> new field in the PER_PHONE table that contained SEQUENCE_NO or something
> similar, but that still was populated through a trigger). That would make
> insert/update/delete into PER_PHONE slightly slower and could cause
> problems if several users inserted records for the same PERSON_ID
> simultaneously. However, your select would be very quick.
>
> Sorry,
> Set
>
>
>
