 |
Return Create A Forum - Home |
 |
--------------------------------------------------------- |
|
ExcelSoft Database Professionals |
 |
https://esdbp.createaforum.com |
|
--------------------------------------------------------- |
|
***************************************************** |
|
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 : [email protected] |
|
|
|
#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 |
|
***************************************************** |
