none
udf affecting performance, any alternatives?

    질문

  • Hi, we have the following udf scattered across our DW and used in 1000's of INSERT statements. When I take this off and just reference the field in every occasion it dramatically improves performance.

    The reason Im told its there is to prevent a DW build failures due to SQL error (eg Conversion Types etc).

    I was wondering if rather than using this approach there was a another not thought of where I don't need to use a udf.

    By taking all the udf functions out I can probably increase our overnight build time by hours but at the same time risk the build failing which I cannot do.

    CAST(dbo.udf_ins_getDatefromDatetime([LZO_BOOKING].[CANCELDTTM]) AS DATETIME) AS [APPOINTMENT_CANCELLED_DATE]

    CREATE FUNCTION [dbo].[udf_INS_GetDateFromDateTime] (
    		@Date		DATETIME
    	)
    RETURNS DATETIME
    
    AS
    
    	BEGIN
    	
    		/*______________________________________________
    		**Declare Local Variables
    		**______________________________________________*/
    		DECLARE @ReturnValue DATETIME
    		
    		/*______________________________________________
    		**Checks for null values
    		**______________________________________________*/
    		IF @Date is null 
    			SET @ReturnValue = null
    		ELSE
    			BEGIN
    		
    		/*______________________________________________
    		**Converts the date to float, rounds it down to the nearest integer
    		**then converts it to datetime
    		**______________________________________________*/
    				SET @ReturnValue = CAST(FLOOR(CAST(@Date AS FLOAT)) AS DATETIME)
    			END
    			
    		/*______________________________________________
    		**Return the result
    		**______________________________________________*/
    		RETURN @ReturnValue
    	END
    GO

    2018년 5월 17일 목요일 오후 1:37

답변

  • UDF are bad for performance and can't find the actual problem though execution plan as well.

    what version of SQL server are you using?

    try using TRY_CAST , it gives NULL if date is not in correct format and thus avoids failure in your original query.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • 답변으로 표시됨 SimonKEvans 2018년 5월 18일 금요일 오전 7:49
    2018년 5월 17일 목요일 오후 1:53
  • Hi SimonKEvans,

    In fact, the script of the function could be equal to the code below in the query statement.

    SELECT  CAST(FLOOR(CAST([LZO_BOOKING].[CANCELDTTM] AS FLOAT)) AS DATETIME) AS [APPOINTMENT_CANCELLED_DATE]

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • 답변으로 표시됨 SimonKEvans 2018년 5월 18일 금요일 오전 8:05
    2018년 5월 18일 금요일 오전 7:50

모든 응답

  • UDF are bad for performance and can't find the actual problem though execution plan as well.

    what version of SQL server are you using?

    try using TRY_CAST , it gives NULL if date is not in correct format and thus avoids failure in your original query.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • 답변으로 표시됨 SimonKEvans 2018년 5월 18일 금요일 오전 7:49
    2018년 5월 17일 목요일 오후 1:53
  • Hi SimonKEvans,

    In fact, the script of the function could be equal to the code below in the query statement.

    SELECT  CAST(FLOOR(CAST([LZO_BOOKING].[CANCELDTTM] AS FLOAT)) AS DATETIME) AS [APPOINTMENT_CANCELLED_DATE]

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • 답변으로 표시됨 SimonKEvans 2018년 5월 18일 금요일 오전 8:05
    2018년 5월 18일 금요일 오전 7:50