Post reply

Function Vs Inline Query

  • 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

  • July 9, 2009 at 1:05 am

    #1022178

    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]

  • July 9, 2009 at 1:18 am

    #1022180

    HI Dave

    Thank you for your quick reply.

  • July 9, 2009 at 3:31 am

    #1022235

    Does you inline query actually work?

    _____________
    Code for TallyGenerator

  • July 10, 2009 at 3:32 am

    #1022913

    Hi

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

    please suggest me if you have any solution

    Thanks.

    Santosh

  • July 10, 2009 at 4:04 pm

    #1023411

    Look at the name of the topic YOU opened.

    You listed 2 solutions.

    One of them does not work.

    So?

    _____________
    Code for TallyGenerator

  • July 12, 2009 at 11:14 pm

    #1023672

    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

  • July 12, 2009 at 11:58 pm

    #1023678

    You've already posted alternative method.

    Why don't use it?

    _____________
    Code for TallyGenerator

  • July 13, 2009 at 2:03 am

    #1023710

    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.

  • July 14, 2009 at 9:21 pm

    #1024799

    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

  • July 15, 2009 at 5:28 am

    #1024927

    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

  • July 15, 2009 at 1:29 pm

    #1025312

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

    It works well for SQL 2005 though! :w00t:

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

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