Alternative to Coalesce for comma delimited field

  • I currently have a query that uses a user defined function that contains coalesce to comma delimit the results of a single field but it is obviously RBAR and takes forever since there are several million records...and it's used for six fields in the query. I would like to find out if there is a way that I could instead utilize the power of a data flow step in SSIS to pull the full dataset, then go get the comma delimited field and merge it or something?

    Anyone have any ideas on which transformations to use to accomplish this? Derived Column then Merge? And would I do it for each of the six fields or can I do it all in one?

    My original query: (obviously stripped and hopefully enough info)

    SELECT DISTINCT

    a.field1,

    a.field2,

    dbo.GetIDByOtherID(a.field3) as OFFICEID,

    dbo.getAIDbyOtherID(a.field3) as AID,

    dbo.getNUMbyOtherID(a.field3) as NUM,

    c.field4,

    dbo.GetOptionStringByOptionNumber('string1',b.field5,a.field6) as CType,

    dbo.GetOptionStringbyOptionNumber('string2',b.field5,a.field6) as LType,

    dbo.GetOptionStringbyOptionNumber('string3',b.field5,a.field6) as LGType

    FROM TableA a

    join TableB b on b.field1 = a.field1

    join TableC c on c.field2 = a.field2

    join TableD d on d.field1 = a.field1

    Here is the first function:

    ALTER FUNCTION [dbo].[GetIDByOtherID]

    (

    @ID int

    )

    RETURNS VARCHAR(200)

    AS

    BEGIN

    DECLARE @res VARCHAR(max)

    SELECT @res = COALESCE(@res + ', ', '') + [FieldA]

    FROM [Table1] INNER JOIN

    [Table2] ON [Table1].[FieldB] = [Table2].[FieldB] INNER JOIN

    [Table3] ON [Table2].[FieldC] = [Table3].[FieldC]

    WHERE [FieldD] = @ID

    ORDER BY [FieldD] asc

    RETURN @res

    END

    The next two functions are exactly the same principal.

    Here is the next function that is used for the bottom three fields:

    ALTER FUNCTION [dbo].[GetOptionStringByOptionNumber]

    (

    @optionID varchar(200),

    @optionNumber int,

    @id int

    )

    RETURNS varchar(200)

    AS

    BEGIN

    DECLARE @result varchar(200)

    SELECT @result = optionstring from options with(nolock) where optionnumber = @optionNumber

    and id = @id and optionID = @optionid

    RETURN @result

    END

    Owner & Principal SQL Server Consultant
    Im Your DBA, Inc.
    https://www.imyourdba.com/

  • I don't think you need to worry about using SSIS - you can drastically speed this up in T-SQL.

    First, make this change to the first 3 functions to quickly create a comma-delimited string:

    DECLARE @res VARCHAR(max); -- declare variable to hold the result

    SELECT @res = stuff(

    (select ', ' + FieldA -- put the commas at the BEGINNING of each field

    FROM [Table1]

    JOIN [Table2]

    ON [Table1].[FieldB] = [Table2].[FieldB]

    JOIN [Table3]

    ON [Table2].[FieldC] = [Table3].[FieldC]

    WHERE [FieldD] = @ID

    ORDER BY [FieldD] asc

    FOR XML PATH('')

    -- using the STUFF function, starting at the first character,

    -- replace the next two characters with an empty string

    ), 1, 2, '') ;

    RETURN @res;

    Next, get rid of the last function, and just call the options table directly in a join. I'm assuming a left join here; it's up to you to figure out the correct call.

    SELECT DISTINCT

    a.field1,

    a.field2,

    dbo.GetIDByOtherID(a.field3) as OFFICEID,

    dbo.getAIDbyOtherID(a.field3) as AID,

    dbo.getNUMbyOtherID(a.field3) as NUM,

    c.field4,

    s1.optionstring,

    s2.optionstring,

    s3.optionstring

    FROM TableA a

    join TableB b on b.field1 = a.field1

    join TableC c on c.field2 = a.field2

    join TableD d on d.field1 = a.field1

    LEFT JOIN options s1 ON s1.optionnumber = 'string1' and s1.id = b.field5 and s1.optionID = a.field6

    LEFT JOIN options s2 ON s1.optionnumber = 'string2' and s1.id = b.field5 and s1.optionID = a.field6

    LEFT JOIN options s3 ON s1.optionnumber = 'string3' and s1.id = b.field5 and s1.optionID = a.field6

    These two modifications should make "forever" a lot shorter for you.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Thank you, I will give it a try. I still need to use SSIS because this is only one step in a pretty large process. But I appreciate the help in trying to speed things up!

    Owner & Principal SQL Server Consultant
    Im Your DBA, Inc.
    https://www.imyourdba.com/

Viewing 3 posts - 1 through 2 (of 2 total)

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