UDF

Question

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 PNAMEORDERVALUE
1001 A 200
1002 B 100
1003 C 300
. . .
. . .
. . .
. . .
25457800AAA 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: 1004356 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 PNAMEORDERVALUE
1001 A 200
1002 B 100
1003 C 300
. . .
. . .
. . .
. . .
25457800AAA 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)

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

Please find the enclosed script for the code

Need to resolve this issue without using UDFS.

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

What about this?

SELECT *

FROM dbo.Product_Test p

WHERE p.PID IN (101,103)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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

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: 246961 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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: 246961 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)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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: 90303 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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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