|Subject||Re: [firebird-support] What is the best way to re-write this Stored Procedure that seems to be SLOW processing?|
|Author||Walter R. Ojeda Valiente|
On Thu, Aug 13, 2015 at 4:49 PM, setysvar setysvar@... [firebird-support] <firstname.lastname@example.org> 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')
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.