This content has been marked as final. Show 4 replies
Thanks sb92075 for taking time.
Application is using such functions ever since it went live on production (~1 year back). It never ran without functions and hence i have no performance data without functions.
Since scalability is a concern in running prod database on commodity hardware, i want to check how much such function calls are contributing to overall service/db performance.
(As of now 10 application servers runs such sqls every now and then. We have a plan of running it from 18 app servers and hence #of executions are almost going to get 2X)
If you can point me towards any specific statistics to look for this function calls, i can certainly share those details.
Mostly functions are neither fast nor slow, it is how they are used or abused that matters to performance.
The example you show should have no noticeable impact on performance, if the DECODE was in the where clause and was used to join to different columns or tables then it could have a big impact
958830 wrote:Calling a million updates in a loop instead of updating a million rows with a single update statement however will absolutely kill performance and will not scale at all.
Update in FOR LOOP (This PL/SQL procedure is called 4 times a day and is processing 1M records (#of iterations in for loop))
If you care at all about performance you will look at that instead of the use of inbuilt functions.
I've never seen any measurable performance impact from built-in functions like DECODE, and I don't think you have any reason to be concerned about that. It is user-defined PL/SQL functions that you should be concerned about, because they can affect your performance either via expensive context switching (from SQL to PL/SQL and back), inefficient code or because of SQL limitations regarding PL/SQL code (e.g. when join conditions involve a user-defined function). But you don't have any in the posted code.
A far more serious problem is, as already pointed out by others, row-by-row processing (e.g. UPDATE inside the loop).