Performance issue using UDF in bulk load jobs (SSIS)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Performance issue using UDF in bulk load jobs...

Performance issue using UDF in bulk load jobs (SSIS)

Rate Topic

Display Mode

Topic Options

Author

Message

Mohammed Mohsin-392707

Posted Sunday, November 11, 2012 11:04 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, November 15, 2012 9:44 AM
Points: 35, Visits: 168

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

Jeff Moden

Posted Sunday, November 11, 2012 11:43 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 32,903, Visits: 26,784

Mohammed Mohsin-392707 (11/11/2012)

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1383456

Randy Knight

Posted Sunday, November 11, 2012 4:12 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 24, Visits: 137

How is the UDF used? In a join or where clause predicate? Select list?

Post #1383480

Mohammed Mohsin-392707

Posted Sunday, November 11, 2012 10:36 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, November 15, 2012 9:44 AM
Points: 35, Visits: 168

In a select list.

Thanks,
-Mohsin

Post #1383499

Dave Ballantyne

Posted Monday, November 12, 2012 12:19 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227

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

Mohammed Mohsin-392707

Posted Monday, November 12, 2012 12:49 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, November 15, 2012 9:44 AM
Points: 35, Visits: 168

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

Jeff Moden

Posted Monday, November 12, 2012 9:15 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 32,903, Visits: 26,784

Mohammed Mohsin-392707 (11/12/2012)

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

Post #1383732

Randy Knight

Posted Monday, November 12, 2012 2:29 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 24, Visits: 137

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 »

Permissions