DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: Performance
       *****************************************************
       #Post#: 101--------------------------------------------------
       Regarding UDF usage
       By: nagesh_prasad_exceldbp Date: November 12, 2014, 1:13 am
       ---------------------------------------------------------
       Hello Team,
       This topic is regarding when to use UDFs in our SPs or Scripts.
       Scenario : We have one UDF, to convert given Time into Seconds
       i.e., hh:mm:ss to seconds
       1. In one of our SP, we are using this function in SELECT
       statement (to convert time to secs). This select statement
       contains almost 700 Records.
       2. But it taking arroung 38 seconds for execution.
       3. If we remove that UDF and implement the logic of UDF within
       SP, then it is getting executed within 2 to 3 secs.
       4. So my question is, when to use UDF?
       Regards,
       Nagesh Prasad
       Email : nagesh.prasad@excelindia.com
       
       #Post#: 109--------------------------------------------------
       Re: Regarding UDF usage
       By: srinivasma_exceldbp Date: November 25, 2014, 12:02 am
       ---------------------------------------------------------
       Dear Nagesh
       1. Scalar UDFs are very costly . As in other programming
       languages it will do stack-in and stack-out
       2. If the UDF is called in a select statement , note that for
       each row, UDF is called making it slow and leading to
       performance issues
       3. Need to check whether UDF code is in multi-step or single
       step
       4.  Note that  SQL Server system functions like getdate(),
       dateadd etc are coded inside in the engine (c and assembly) .
       They are optimized to run fast
       Now to over come this
       1. Check the code , see you can reduce the number of steps
       2. Try changing to return as table with one row , one column .
       But note that you have change the calling of function to outer
       apply/cross apply
       3.  If possible , use CLR to code in C or VB
       *****************************************************