June 28, 2012 at 12:34 pm
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?
June 28, 2012 at 2:16 pm
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
Change is inevitable... Change for the better is not.
June 29, 2012 at 5:03 am
Please find the enclosed script for the code
Need to resolve this issue without using UDFS.
June 29, 2012 at 5:46 am
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
June 29, 2012 at 6:08 am
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
June 29, 2012 at 6:20 am
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
June 29, 2012 at 6:21 am
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
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
June 29, 2012 at 6:23 am
I need a query without split function
June 29, 2012 at 6:26 am
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?
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
June 29, 2012 at 7:16 am
It can be max 50 values i can pass these values
June 29, 2012 at 7:19 am
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].
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
June 29, 2012 at 7:25 am
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.
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
June 29, 2012 at 7:33 am
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.What about some dynamic SQL? Pseudo code:
@sql = 'select * ... where PID in (' & @IDList & ')'
execute (@sql)
Technically Phil, yes...but...never mind 😎
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
June 29, 2012 at 7:52 am
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
June 29, 2012 at 8:01 am
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.
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply