August 18, 2010 at 8:59 pm
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/
August 18, 2010 at 9:36 pm
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
August 19, 2010 at 7:50 am
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