Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performance issue using UDF in bulk load jobs (SSIS) Expand / Collapse
Author
Message
Posted Sunday, November 11, 2012 11:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 2, 2013 7:46 AM
Points: 35, Visits: 171
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
Post #1383455
Posted Sunday, November 11, 2012 11:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 35,403, Visits: 31,964
Mohammed Mohsin-392707 (11/11/2012)
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.



The function is a scalar UDF which is a performance problem in and of itself. That, notwithstanding, things don't just suddenly start running slower unless something actually did change. Perhaps the target table of your load finally reached a tipping point where the existing statistics are no longer valid causing a change for the worse in the execution plan. Perhaps the server is using more memory for other things than it used to.

I recommend that you first rework the scalar UDF and the code or process that uses it to be an Inline Table Valued function that returns a scalar value. That will take care of some of the performance problem. Once that is done, I'd re-evaluate any slownness that remains.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1383456
Posted Sunday, November 11, 2012 4:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:45 PM
Points: 24, Visits: 152
How is the UDF used? In a join or where clause predicate? Select list?
Post #1383480
Posted Sunday, November 11, 2012 10:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 2, 2013 7:46 AM
Points: 35, Visits: 171
In a select list.

Thanks,
-Mohsin
Post #1383499
Posted Monday, November 12, 2012 12:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
Since SQL Server is generally quite poor a string manipulation, the obvious thing to do would be to move this logic into the layer that is most approriate ie the SSIS task.

Will be a relatively trivial thing to do.




Clear Sky SQL
My Blog
Kent user group
Post #1383514
Posted Monday, November 12, 2012 12:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 2, 2013 7:46 AM
Points: 35, Visits: 171
I am using this SQL within the data flow task of SSIS package. I am doing simple select from a stage table and inserting into the base table. Wherever date coumns are there in stage, I am using UDF to validate and return.

Any quick things I can take a look? Changing any thing related to UDF is a huge task as it has been used in more than 500 locations.

Thanks,
-Mohsin
Post #1383527
Posted Monday, November 12, 2012 9:15 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 35,403, Visits: 31,964
Mohammed Mohsin-392707 (11/12/2012)
I am using this SQL within the data flow task of SSIS package. I am doing simple select from a stage table and inserting into the base table. Wherever date coumns are there in stage, I am using UDF to validate and return.

Any quick things I can take a look? Changing any thing related to UDF is a huge task as it has been used in more than 500 locations.

Thanks,
-Mohsin


Then create a new iTVF UDF just for this task to start with. The current UDF is a scalar UDF and will always be a performance problem.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1383732
Posted Monday, November 12, 2012 2:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:45 PM
Points: 24, Visits: 152
I agree with Jeff. Or for that matter since this is just in the select list, that scalar function can be turned into a case statement very easily.

Post #1383881
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse