UDF

Question

Post reply

UDF

satishthota

SSC Eights!

Points: 959
More actions
June 28, 2012 at 12:34 pm

#392623

How to avoid the user defined functions in Where Clause in sql server 2005?

Im getting performance issues when iam using the user defined functions(In Line functions) in where clause?

Scenario:

I had table with 2,54,57,800 ROWS and Iam passing multiple PID values with comma separated by using udf(In line functions) in where condition

PID PNAME ORDERVALUE

1001 A 200

1002 B 100

1003 C 300

. . .

. . .

. . .

. . .

25457800 AAA 400

Can you give me examples without using udfs for the above scenario?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

satishthota (6/28/2012)

How to avoid the user defined functions in Where Clause in sql server 2005?

Im getting performance issues when iam using the user defined functions(In Line functions) in where clause?

Scenario:

I had table with 2,54,57,800 ROWS and Iam passing multiple PID values with comma separated by using udf(In line functions) in where condition

PID PNAME ORDERVALUE

1001 A 200

1002 B 100

1003 C 300

. . .

. . .

. . .

. . .

25457800 AAA 400

Can you give me examples without using udfs for the above scenario?

Please post the code in question and the code for the UDF. Posting an example of the CSV parameter would be helpful, as well.

--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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

satishthota SSC Eights! Points: 959 More actions · Answer 2

satishthota

SSC Eights!

Points: 959

June 29, 2012 at 5:03 am

#1507044

Please find the enclosed script for the code

Need to resolve this issue without using UDFS.

Phil Parkin SSC Guru Points: 245813 More actions · Answer 3

Phil Parkin

SSC Guru

Points: 245813

June 29, 2012 at 5:46 am

#1507060

What about this?

SELECT *



FROM dbo.Product_Test p

WHERE p.PID IN (101,103)

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

satishthota SSC Eights! Points: 959 More actions · Answer 4

satishthota

SSC Eights!

Points: 959

June 29, 2012 at 6:08 am

#1507072

I don't want to use static values

for example i had given two values in the query

so it can two values or more than two values

Phil Parkin SSC Guru Points: 245813 More actions · Answer 5

Hundreds of ways of skinning this cat.

Here's one:

declare @a table (PID int primary key clustered)



insert @a (PID) select 101 union select 103

--Obviously expand this / modify it as needed to include whatever IDs are required.

--Use a temp table if this may contain hundreds of IDs

SELECT p.*

FROM dbo.Product_Test p

join @a IDs on p.PID = IDs.PID

No doubt this will not suit you either. If not, you need to give more detail about what your (design) constraints are.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

SELECT Data



INTO #PIDs

FROM dbo.Split('101,103',',')

CREATE CLUSTERED INDEX [xyz] ON #PIDs (Data) 

SELECT *

FROM dbo.Product_Test p

INNER JOIN #PIDs d ON d.Data = p.PID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

satishthota SSC Eights! Points: 959 More actions · Answer 7

satishthota

SSC Eights!

Points: 959

June 29, 2012 at 6:23 am

#1507083

I need a query without split function

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

satishthota (6/29/2012)

I need a query without split function

What is the maximum number of discrete values to be found in the comma-delimited string?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

satishthota SSC Eights! Points: 959 More actions · Answer 9

satishthota

SSC Eights!

Points: 959

June 29, 2012 at 7:16 am

#1507121

It can be max 50 values i can pass these values

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

If you'd said "less than 6" then you would have had alternatives options to a function. With a maximum of about 50 items in the list, then I reckon you don't have a choice - apart from choosing the fastest splitter around.

Edit: check out this article[/url].

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Phil Parkin SSC Guru Points: 245813 More actions · Answer 11

ChrisM@Work (6/29/2012)

If you'd said "less than 6" then you would have had alternatives options to a function. With a maximum of about 50 items in the list, then I reckon you don't have a choice - apart from choosing the fastest splitter around.

Edit: check out this article[/url].

What about some dynamic SQL? Pseudo code:

@sql = 'select * ... where PID in (' & @IDList & ')'

execute (@sql)

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Phil Parkin (6/29/2012)

ChrisM@Work (6/29/2012)

If you'd said "less than 6" then you would have had alternatives options to a function. With a maximum of about 50 items in the list, then I reckon you don't have a choice - apart from choosing the fastest splitter around.

Edit: check out this article[/url].

What about some dynamic SQL? Pseudo code:

@sql = 'select * ... where PID in (' & @IDList & ')'
execute (@sql)

Technically Phil, yes...but...never mind 😎

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 13

satishthota (6/29/2012)

I need a query without split function

Is the requirement to remove the UDF something that is mandated by company policy? Or, are you looking at the function as a performance issue and determining that removing the function will improve performance?

If the latter, it isn't the function that is causing the performance problem per se. It is how you are using the function - and probably how that function is written.

Several techniques are available for improving performance using the function. Using the function in a WHERE clause is definitely your problem - and moving that to either create a temp table and JOIN to the temp table or directly joining (CROSS APPLY) to the function will imiprove performance.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

Using an inline function may help somewhat. Here's the definition of your existing splitter:

--User Defined Function (InLine)



CREATE FUNCTION dbo.Split

(

	@RowData nvarchar(2000),

	@SplitOn nvarchar(5)

)

RETURNS @RtnValue table

(

	Id int identity(1,1),

	Data nvarchar(100)

)

AS

BEGIN

	Declare @Cnt int

	Set @Cnt = 1

	While (Charindex(@SplitOn,@RowData)>0)

	Begin

		Insert Into @RtnValue (data)

		Select

			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

		Set @Cnt = @Cnt + 1

	End

	Insert Into @RtnValue (data)

	Select Data = ltrim(rtrim(@RowData))

	Return

END

Although the comment at the top of the definition states (inline), it isn't, it's a multistatement TVF.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden