Function Vs Inline Query

Question

Function Vs Inline Query

santoooo

SSCommitted

Points: 1999
More actions
July 9, 2009 at 12:56 am

#212193

Dear All,
I am very curious to know whichone will give the better performance either Funtion or Inline Query.
My requirement is
i am having a query(which will return millions of records) in which i have called funciton that will return the comma seperated value for the given id
Here is the funciton code.
CREATE FUNCTION [dbo].[fn_SplitByComma]
(
@Value Bigint
) RETURNS varchar(8000)
AS BEGIN
DECLARE @Result varchar(8000)
SET @Result = ''
SELECT @Result = @Result + CASE WHEN LEN(@Result)>0 THEN ', ' ELSE ''
END + <>
FROM <>
WHERE id= @Value
RETURN @Result
END
.
I am using this in a select query which will return millions of records
as
SELECT id, dbo.fn_SplitByComma(id) as commaseperatedvalue
FROM <>
Could you please tell me whichone is the best one
what if i use inline query instead of function..
SELECT id,
(
SELECT CASE WHEN LEN(<>)>0 THEN ', ' ELSE '' END + <>
FROM <>
WHERE id= @Value
)
commaseperatedvalue
FROM <>
Thanks,
Santosh

Viewing 12 posts - 1 through 11 (of 11 total)

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Hi , i would suspect that the inline version performs better. Calling a scalar UDF carries a bit of overhead, but SQLProfiler is the tool to use to collect your own evidence of which performs better in your environment on your data.

Clear Sky SQL
My Blog[/url]

santoooo SSCommitted Points: 1999 More actions · Answer 2

santoooo

SSCommitted

Points: 1999

July 9, 2009 at 1:18 am

#1022180

HI Dave

Thank you for your quick reply.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Does you inline query actually work?

_____________
Code for TallyGenerator

santoooo SSCommitted Points: 1999 More actions · Answer 4

Hi

No, I am trying for that how to get comma seperated result,

please suggest me if you have any solution

Thanks.

Santosh

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Look at the name of the topic YOU opened.

You listed 2 solutions.

One of them does not work.

So?

_____________
Code for TallyGenerator

santoooo SSCommitted Points: 1999 More actions · Answer 6

Hi,

I taught it will work, so i posted. and also i taught some alternative method will be given by some experts if am wrong. please give me the solution if any.

Thanks

-- Santosh

Sergiy SSC Guru Points: 110208 More actions · Answer 7

You've already posted alternative method.

Why don't use it?

_____________
Code for TallyGenerator

santoooo SSCommitted Points: 1999 More actions · Answer 8

Hi,

I have created a view by using the splitbycomma funtion, it is take more time to execute, coz my table contains millions of records, that's why iam searching for alternative like inline query..

I think inline query is not possible in this scenario right,, i have to go with the funtion only na.

ok thank you for your suggestions

--Santosh.

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 9

This will return a comma separated list:

SELECT ',' + [columnName]

FROM [TableName]

WHERE id = @value

FOR XML PATH ('')

I have tested a similar query which works ok

santoooo SSCommitted Points: 1999 More actions · Answer 10

Hi Ivanna Noh,

I want this feature in SQL 2000,

I am trying but it is not supporting it is saying that

Server: Msg 170, Level 15, State 1, Line 3

Line 3: Incorrect syntax near 'PATH'.

I think XML PATH isnot supported in SQL2000

Thanks

Santosh

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 11

my mistake - I didn't check to see which version of SQl you were using - apologies

It works well for SQL 2005 though! :w00t: