SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance issue using UDF in bulk load jobs (SSIS)


Performance issue using UDF in bulk load jobs (SSIS)

Author
Message
Mohammed Mohsin-392707
Mohammed Mohsin-392707
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 179
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85734 Visits: 41091
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Randy Knight
Randy Knight
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 155
How is the UDF used? In a join or where clause predicate? Select list?
Mohammed Mohsin-392707
Mohammed Mohsin-392707
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 179
In a select list.

Thanks,
-Mohsin
Dave Ballantyne
Dave Ballantyne
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2880 Visits: 8370
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
Mohammed Mohsin-392707
Mohammed Mohsin-392707
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 179
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85734 Visits: 41091
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Randy Knight
Randy Knight
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 155
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search