SQL Server Performance

Performance issue with UDF suring bulk loads (SSIS)

Discussion in 'ALL SQL SERVER QUESTIONS' started by Mohsin.hsn, Nov 11, 2012.

  1. Mohsin.hsn New Member

    Hi All,
    We have bulk load jobs configured using SSIS packages. All the load jobs were working perfectly fine until 2 days back. Suddenly, our load jobs are taking way longer than the normal time. The issue is identified to be the use of UDF as shown below,
    Note: These jobs were working fine since 3 years without any issues. We load around 16 GB of data each day.
    No changes to the server and\or SQL Server itself.
    Any help would be greatly appreciated.

    Thanks,
    -Mohsin
    ALTER FUNCTION [dbo].[convertdate_null] (@input varchar(50))
    RETURNS VARCHAR(12)
    AS
    BEGIN
    DECLARE @output DATETIME;
    SELECT @output = CASE WHEN (ISDATE ( ( CASE WHEN Left(replace(ltrim(rtrim(@input)),'+',''), 1) =1
    THEN '20'
    ELSE '19'
    END + Right(ltrim(rtrim(@input)), 6))
    ) = 1)
    THEN CONVERT(VARCHAR(12),CONVERT (DATETIME, CASE WHEN Left(replace(ltrim(rtrim(@input)),'+',''),1) = 1
    THEN '20'
    ELSE '19'
    END + Right(ltrim(rtrim(@input)), 6) ) )

    ELSE
    null
    END
    RETURN @output
    END

  2. Shehap MVP, MCTS, MCITP SQL Server

    First Welcome to Forums,

    Ultimately speaking about UDFs , they may participate in performance slowness cases either they are scalar function or TVF (Table Valued Function) if they aren’t used properly within your T-SQL query so you can learn more about these cases at my blog :

    http://www.sqlserver-performance-tu.../12926989-t-sql-performance-optimization-4-6-

    Kindly bear in your mind that such issues may appear after 3 years due to the increase of stress on DB due to either growth of DB or growth of concurrent users hitting the same T-SQL query at the same time or perhaps some other T-SQL Query is running on the same DB server and consuming massive IO /CPU cost

    Therefore, kindly post your T-SQL Query that use this UDF with the schema design of relevant tables to help you for determine which case is relevant to your T-SQL Query then we can come out with the appropriate T-SQL/Index enhancements and let us see where we can get then..

Share This Page