July 27, 2012 at 2:48 am
OK, I built my own sample data.
SELECT TOP 4000000 CAST(NEWID() AS VARCHAR(36)) AS concatenateMe
INTO dbo.myTable
FROM master.sys.all_columns a, master.sys.all_columns b, master.sys.all_columns c
4 million rows.
Here's how we'll concatenate them the first time (the FOR XML method): -
DECLARE @holder VARCHAR(MAX);
SELECT @holder = STUFF((SELECT ',' + concatenateMe
FROM dbo.myTable
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
SQL Server Execution Times:
CPU time = 9235 ms, elapsed time = 9263 ms.
9 seconds or thereabouts for 4 million records.
Now the more traditional method: -
DECLARE @holder VARCHAR(MAX);
SET @holder = '';
SELECT @holder = @holder + ',' + concatenateMe
FROM dbo.myTable
<--Still running after 3 minutes, will edit my post when it completes. But so far that makes it 20x slower than the FOR XML method-->
I've stopped it after 10 minutes, which means it more than 60x slower than the FOR XML method on my system.
Those results are without any indexes, I could re-run with indexes to see what difference it makes if you'd like.
July 27, 2012 at 3:02 am
The tables are as follows
CREATE TABLE [dbo].table1(
[PID] [int] NOT NULL,
[CID] [int] NOT NULL,
CONSTRAINT [PK_tbl1] PRIMARY KEY CLUSTERED ([PID] ASC,[CID] ASC)
)
GO
CREATE TABLE [dbo].[table2](
[PID] [int] NOT NULL,
[SID] [bigint] NOT NULL,
CONSTRAINT [PK_tbl2] PRIMARY KEY CLUSTERED ([PID] ASC,[SID] ASC)
)
GO
CREATE TABLE [dbo].[table3](
[SID] [bigint] NOT NULL,
[CID] [int] NOT NULL,
CONSTRAINT [PK_tbl3] PRIMARY KEY CLUSTERED([SID] ASC,[CID] ASC)
)
GO
This is my adaptation of John's solution
CREATE FUNCTION [dbo].[func](@SID int)
RETURNS varchar(max)
AS
BEGIN
DECLARE @descr VARCHAR(MAX) = ''
;WITH CTE AS
(
SELECT
csg.CID
FROM
table1 csg
JOIN
table2 map
ON
map.PID = csg.PID
WHERE
map.SID = @sid
UNION
SELECT
cs.CID
FROM
table3 cs
WHERE
cs.SID = @sid
)
SELECT @descr = @descr +', '+ CONVERT(VARCHAR,ContentID) FROM CTE ORDER BY ContentID
SET @descr = RIGHT(@descr,LEN(@descr)-2)
RETURN (@descr)
END
This is my adaptation of Lynn's function
CREATE FUNCTION [dbo].[func2](@SID int)
RETURNS @CommaList TABLE (CommaList VARCHAR(MAX))
AS
BEGIN
;WITH CTE AS
(
SELECT
csg.CID
FROM
table1 csg
JOIN
table2 map
ON
map.PID = csg.PID
WHERE
map.SID = @sid
UNION
SELECT
cs.CID
FROM
table3 cs
WHERE
cs.SID = @sid
),
CTE2 AS(
SELECT CommaList = STUFF((
SELECT ', ' + CONVERT(VARCHAR,c.CID)
FROM CTE c
ORDER BY c.CID
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,2,'')
FROM CTE
)
INSERT INTO @CommaList SELECT CommaList FROM CTE2 GROUP BY CommaList
RETURN
END
Sample data attached as its the whole data set as they are just int values and not sensitive, but as there are 4.2 million rows dont really fancy posting that in a post.
Expected result is like follows
For STD 10 it could return a single column with 1 row like below
25536, 25542, 25740, 26323, 26936, 27398, 27457, 27461, 28564, 28801, 28969, 29152, 29185, 29186, 29187, 29188, 29189, 29408, 29409, 29422, 29482, 29484, 29506, 29508, 29509, 29561, 29565, 29570, 29576, 29798, 29823, 29837, 29889, 30124, 30137, 30143, 30265, 30361, 30467, 30478, 30848, 30907, 30945, 31203, 31206, 31207, 31208, 31209, 31210, 31211, 31281, 31313, 31386, 31448, 31463, 31496, 31497, 31499, 31811, 31848, 31849, 31871, 31961, 32051, 32061, 32220, 32237, 32295, 34222, 34237, 34316, 34318, 34526, 34711, 34759, 34771, 34856, 34861, 35127, 35268, 35806, 36080, 36185, 36973, 37457, 43200
July 27, 2012 at 3:19 am
anthony.green (7/27/2012)
The tables are as follows
CREATE TABLE [dbo].table1(
[PID] [int] NOT NULL,
[CID] [int] NOT NULL,
CONSTRAINT [PK_tbl1] PRIMARY KEY CLUSTERED ([PID] ASC,[CID] ASC)
)
GO
CREATE TABLE [dbo].[table2](
[PID] [int] NOT NULL,
[SID] [bigint] NOT NULL,
CONSTRAINT [PK_tbl2] PRIMARY KEY CLUSTERED ([PID] ASC,[SID] ASC)
)
GO
CREATE TABLE [dbo].[table3](
[SID] [bigint] NOT NULL,
[CID] [int] NOT NULL,
CONSTRAINT [PK_tbl3] PRIMARY KEY CLUSTERED([SID] ASC,[CID] ASC)
)
GO
This is my adaptation of John's solution
CREATE FUNCTION [dbo].[func](@SID int)
RETURNS varchar(max)
AS
BEGIN
DECLARE @descr VARCHAR(MAX) = ''
;WITH CTE AS
(
SELECT
csg.CID
FROM
table1 csg
JOIN
table2 map
ON
map.PID = csg.PID
WHERE
map.SID = @sid
UNION
SELECT
cs.CID
FROM
table3 cs
WHERE
cs.SID = @sid
)
SELECT @descr = @descr +', '+ CONVERT(VARCHAR,ContentID) FROM CTE ORDER BY ContentID
SET @descr = RIGHT(@descr,LEN(@descr)-2)
RETURN (@descr)
END
This is my adaptation of Lynn's function
CREATE FUNCTION [dbo].[func2](@SID int)
RETURNS @CommaList TABLE (CommaList VARCHAR(MAX))
AS
BEGIN
;WITH CTE AS
(
SELECT
csg.CID
FROM
table1 csg
JOIN
table2 map
ON
map.PID = csg.PID
WHERE
map.SID = @sid
UNION
SELECT
cs.CID
FROM
table3 cs
WHERE
cs.SID = @sid
),
CTE2 AS(
SELECT CommaList = STUFF((
SELECT ', ' + CONVERT(VARCHAR,c.CID)
FROM CTE c
ORDER BY c.CID
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,2,'')
FROM CTE
)
INSERT INTO @CommaList SELECT CommaList FROM CTE2 GROUP BY CommaList
RETURN
END
Sample data attached as its the whole data set as they are just int values and not sensitive, but as there are 4.2 million rows dont really fancy posting that in a post.
Expected result is like follows
For STD 10 it could return a single column with 1 row like below
25536, 25542, 25740, 26323, 26936, 27398, 27457, 27461, 28564, 28801, 28969, 29152, 29185, 29186, 29187, 29188, 29189, 29408, 29409, 29422, 29482, 29484, 29506, 29508, 29509, 29561, 29565, 29570, 29576, 29798, 29823, 29837, 29889, 30124, 30137, 30143, 30265, 30361, 30467, 30478, 30848, 30907, 30945, 31203, 31206, 31207, 31208, 31209, 31210, 31211, 31281, 31313, 31386, 31448, 31463, 31496, 31497, 31499, 31811, 31848, 31849, 31871, 31961, 32051, 32061, 32220, 32237, 32295, 34222, 34237, 34316, 34318, 34526, 34711, 34759, 34771, 34856, 34861, 35127, 35268, 35806, 36080, 36185, 36973, 37457, 43200
One last thing, can you show me the code you are running against the tables? Also, I already see why the tvf is so slow, you have it coded as a multi-statement tvf, not an in-line tvf.
July 27, 2012 at 3:21 am
Sure, I am just obfuscating the view definition and will supply DDL and sample data for the tables in the view. So that you have the bigger picture to work on.
July 27, 2012 at 3:25 am
I'm trying to figure out how the tables relate first, so I can figure out how the final result set is to be built. Think I could get 5 or 10 rows of data for the tables and expected results based on those?
July 27, 2012 at 3:35 am
Hope this help Lynn, its only for 1 STD but should hopefully be a small enough sample of data to see how the tables relate.
Sample data for STD ID 4946
INSERT INTO table1 VALUES
(1246,148218),
(1338,148218)
INSERT INTO table2 VALUES
(1246,4946),
(1338,4946)
INSERT INTO table3 VALUES
(4946,25510),
(4946,25529),
(4946,25535),
(4946,25542),
(4946,25545),
(4946,25546),
(4946,25577),
(4946,25578),
(4946,25589),
(4946,25594),
(4946,25602),
(4946,25618),
(4946,25619),
(4946,25622),
(4946,25623),
(4946,25628),
(4946,25642),
(4946,25643),
(4946,25646),
(4946,25731),
(4946,25734),
(4946,25740),
(4946,25785),
(4946,25830),
(4946,25876),
(4946,25977),
(4946,25978),
(4946,25980),
(4946,25981),
(4946,25987),
(4946,25989),
(4946,25992),
(4946,26004),
(4946,26005),
(4946,26085),
(4946,26091),
(4946,26177),
(4946,26264),
(4946,26274),
(4946,26276),
(4946,26292),
(4946,26294),
(4946,26299),
(4946,26300),
(4946,26312),
(4946,26313),
(4946,26314),
(4946,26315),
(4946,26316),
(4946,26317),
(4946,26318),
(4946,26319),
(4946,26337),
(4946,26338),
(4946,26339),
(4946,26340),
(4946,26341),
(4946,26345),
(4946,26357),
(4946,26358),
(4946,26936),
(4946,26937),
(4946,27201),
(4946,27254),
(4946,27398),
(4946,27409),
(4946,27410),
(4946,27411),
(4946,27412),
(4946,27413),
(4946,27457),
(4946,27458),
(4946,27459),
(4946,27460),
(4946,27461),
(4946,27462),
(4946,27463),
(4946,27484),
(4946,27486),
(4946,27492),
(4946,27510),
(4946,27511),
(4946,27581),
(4946,27679),
(4946,27681),
(4946,27715),
(4946,27716),
(4946,27717),
(4946,27718),
(4946,27720),
(4946,27721),
(4946,27725),
(4946,27726),
(4946,27874),
(4946,27875),
(4946,27876),
(4946,27877),
(4946,27878),
(4946,27879),
(4946,27880),
(4946,27881),
(4946,27882),
(4946,27883),
(4946,27884),
(4946,27885),
(4946,27886),
(4946,27887),
(4946,27888),
(4946,27889),
(4946,27890),
(4946,27892),
(4946,27893),
(4946,27894),
(4946,27895),
(4946,27896),
(4946,27897),
(4946,27898),
(4946,27899),
(4946,27900),
(4946,27901),
(4946,27902),
(4946,27903),
(4946,27904),
(4946,27905),
(4946,27906),
(4946,27907),
(4946,27908),
(4946,27909),
(4946,27910),
(4946,27911),
(4946,27912),
(4946,27913),
(4946,27914),
(4946,27915),
(4946,27916),
(4946,27917),
(4946,27969),
(4946,27979),
(4946,28009),
(4946,28075),
(4946,28118),
(4946,28136),
(4946,28157),
(4946,28172),
(4946,28333),
(4946,28337),
(4946,28338),
(4946,28358),
(4946,28390),
(4946,28391),
(4946,28392),
(4946,28393),
(4946,28394),
(4946,28564),
(4946,28579),
(4946,28645),
(4946,28670),
(4946,28677),
(4946,28703),
(4946,28705),
(4946,28798),
(4946,28801),
(4946,28837),
(4946,28854),
(4946,28865),
(4946,28869),
(4946,28898),
(4946,28969),
(4946,28997),
(4946,29013),
(4946,29027),
(4946,29142),
(4946,29152),
(4946,29160),
(4946,29185),
(4946,29186),
(4946,29187),
(4946,29188),
(4946,29189),
(4946,29364),
(4946,29381),
(4946,29408),
(4946,29409),
(4946,29422),
(4946,29482),
(4946,29484),
(4946,29506),
(4946,29508),
(4946,29509),
(4946,29561),
(4946,29565),
(4946,29570),
(4946,29576),
(4946,29589),
(4946,29798),
(4946,29823),
(4946,29836),
(4946,29837),
(4946,29841),
(4946,29843),
(4946,29844),
(4946,29872),
(4946,29889),
(4946,29920),
(4946,30114),
(4946,30124),
(4946,30136),
(4946,30143),
(4946,30203),
(4946,30214),
(4946,30265),
(4946,30361),
(4946,30467),
(4946,30478),
(4946,30496),
(4946,30586),
(4946,30677),
(4946,30796),
(4946,30843),
(4946,30848),
(4946,30885),
(4946,30906),
(4946,30907),
(4946,30909),
(4946,30911),
(4946,30915),
(4946,30924),
(4946,30945),
(4946,31141),
(4946,31203),
(4946,31206),
(4946,31207),
(4946,31208),
(4946,31209),
(4946,31210),
(4946,31211),
(4946,31219),
(4946,31280),
(4946,31281),
(4946,31313),
(4946,31323),
(4946,31386),
(4946,31391),
(4946,31396),
(4946,31423),
(4946,31448),
(4946,31450),
(4946,31463),
(4946,31478),
(4946,31496),
(4946,31497),
(4946,31498),
(4946,31499),
(4946,31517),
(4946,31518),
(4946,31519),
(4946,31520),
(4946,31521),
(4946,31524),
(4946,31525),
(4946,31527),
(4946,31528),
(4946,31529),
(4946,31530),
(4946,31532),
(4946,31534),
(4946,31535),
(4946,31537),
(4946,31538),
(4946,31544),
(4946,31596),
(4946,31607),
(4946,31618),
(4946,31660),
(4946,31661),
(4946,31692),
(4946,31693),
(4946,31799),
(4946,31811),
(4946,31848),
(4946,31849),
(4946,31859),
(4946,31866),
(4946,31871),
(4946,31873),
(4946,31926),
(4946,31950),
(4946,31961),
(4946,31970),
(4946,32029),
(4946,32051),
(4946,32061),
(4946,32093),
(4946,32116),
(4946,32216),
(4946,32220),
(4946,32237),
(4946,32319),
(4946,32321),
(4946,32489),
(4946,32847),
(4946,33110),
(4946,33134),
(4946,33176),
(4946,33359),
(4946,33420),
(4946,33885),
(4946,33929),
(4946,34103),
(4946,34107),
(4946,34174),
(4946,34237),
(4946,34254),
(4946,34307),
(4946,34316),
(4946,34318),
(4946,34491),
(4946,34526),
(4946,34574),
(4946,34582),
(4946,34680),
(4946,34686),
(4946,34691),
(4946,34692),
(4946,34693),
(4946,34694),
(4946,34695),
(4946,34696),
(4946,34698),
(4946,34699),
(4946,34700),
(4946,34701),
(4946,34759),
(4946,34771),
(4946,34831),
(4946,34856),
(4946,34861),
(4946,34941),
(4946,35018),
(4946,35026),
(4946,35089),
(4946,35127),
(4946,35150),
(4946,35234),
(4946,35268),
(4946,35286),
(4946,35316),
(4946,35317),
(4946,35318),
(4946,35361),
(4946,35459),
(4946,35512),
(4946,35523),
(4946,35547),
(4946,35552),
(4946,35581),
(4946,35715),
(4946,35721),
(4946,35747),
(4946,35764),
(4946,35843),
(4946,35871),
(4946,36033),
(4946,36080),
(4946,36148),
(4946,36175),
(4946,36185),
(4946,36255),
(4946,36306),
(4946,36310),
(4946,36339),
(4946,36670),
(4946,36687),
(4946,36866),
(4946,36942),
(4946,36968),
(4946,36973),
(4946,37561),
(4946,37622),
(4946,43200),
(4946,43247),
(4946,44195)
Expected outcome
25510, 25529, 25535, 25542, 25545, 25546, 25577, 25578, 25589, 25594, 25602, 25618, 25619, 25622, 25623, 25628, 25642, 25643, 25646, 25731, 25734, 25740, 25785, 25830, 25876, 25977, 25978, 25980, 25981, 25987, 25989, 25992, 26004, 26005, 26085, 26091, 26177, 26264, 26274, 26276, 26292, 26294, 26299, 26300, 26312, 26313, 26314, 26315, 26316, 26317, 26318, 26319, 26337, 26338, 26339, 26340, 26341, 26345, 26357, 26358, 26936, 26937, 27201, 27254, 27398, 27409, 27410, 27411, 27412, 27413, 27457, 27458, 27459, 27460, 27461, 27462, 27463, 27484, 27486, 27492, 27510, 27511, 27581, 27679, 27681, 27715, 27716, 27717, 27718, 27720, 27721, 27725, 27726, 27874, 27875, 27876, 27877, 27878, 27879, 27880, 27881, 27882, 27883, 27884, 27885, 27886, 27887, 27888, 27889, 27890, 27892, 27893, 27894, 27895, 27896, 27897, 27898, 27899, 27900, 27901, 27902, 27903, 27904, 27905, 27906, 27907, 27908, 27909, 27910, 27911, 27912, 27913, 27914, 27915, 27916, 27917, 27969, 27979, 28009, 28075, 28118, 28136, 28157, 28172, 28333, 28337, 28338, 28358, 28390, 28391, 28392, 28393, 28394, 28564, 28579, 28645, 28670, 28677, 28703, 28705, 28798, 28801, 28837, 28854, 28865, 28869, 28898, 28969, 28997, 29013, 29027, 29142, 29152, 29160, 29185, 29186, 29187, 29188, 29189, 29364, 29381, 29408, 29409, 29422, 29482, 29484, 29506, 29508, 29509, 29561, 29565, 29570, 29576, 29589, 29798, 29823, 29836, 29837, 29841, 29843, 29844, 29872, 29889, 29920, 30114, 30124, 30136, 30143, 30203, 30214, 30265, 30361, 30467, 30478, 30496, 30586, 30677, 30796, 30843, 30848, 30885, 30906, 30907, 30909, 30911, 30915, 30924, 30945, 31141, 31203, 31206, 31207, 31208, 31209, 31210, 31211, 31219, 31280, 31281, 31313, 31323, 31386, 31391, 31396, 31423, 31448, 31450, 31463, 31478, 31496, 31497, 31498, 31499, 31517, 31518, 31519, 31520, 31521, 31524, 31525, 31527, 31528, 31529, 31530, 31532, 31534, 31535, 31537, 31538, 31544, 31596, 31607, 31618, 31660, 31661, 31692, 31693, 31799, 31811, 31848, 31849, 31859, 31866, 31871, 31873, 31926, 31950, 31961, 31970, 32029, 32051, 32061, 32093, 32116, 32216, 32220, 32237, 32319, 32321, 32489, 32847, 33110, 33134, 33176, 33359, 33420, 33885, 33929, 34103, 34107, 34174, 34237, 34254, 34307, 34316, 34318, 34491, 34526, 34574, 34582, 34680, 34686, 34691, 34692, 34693, 34694, 34695, 34696, 34698, 34699, 34700, 34701, 34759, 34771, 34831, 34856, 34861, 34941, 35018, 35026, 35089, 35127, 35150, 35234, 35268, 35286, 35316, 35317, 35318, 35361, 35459, 35512, 35523, 35547, 35552, 35581, 35715, 35721, 35747, 35764, 35843, 35871, 36033, 36080, 36148, 36175, 36185, 36255, 36306, 36310, 36339, 36670, 36687, 36866, 36942, 36968, 36973, 37561, 37622, 43200, 43247, 44195, 148218
July 27, 2012 at 3:42 am
One piece is missing for my understanding, without the concatenation if I were to query the tables, how do they relate to one another, and based on the sample data, is only one row supposed to be returned or two or more based on data from the other tables.
There is a reason I don't do telephone support; it really helps me to see what I am trying to do.
July 27, 2012 at 3:46 am
Lynn Pettis (7/27/2012)
One piece is missing for my understanding, without the concatenation if I were to query the tables, how do they relate to one another, and based on the sample data, is only one row supposed to be returned or two or more based on data from the other tables.There is a reason I don't do telephone support; it really helps me to see what I am trying to do.
Are you not just doing this?
DECLARE @holder VARCHAR(MAX), @sid INT = 4946;
SELECT @holder = STUFF((SELECT ',' + CAST(CID AS VARCHAR(19))
FROM (SELECT csg.CID
FROM table1 csg
INNER JOIN table2 map ON map.PID = csg.PID
WHERE map.SID = @sid
UNION
SELECT cs.CID
FROM table3 cs
WHERE cs.SID = @sid) a
ORDER BY CID
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
Or have I over-simplified?
July 27, 2012 at 3:56 am
We have a hierarchy structure which is what is worked out in tables 1 and 2, where a CID maybe me mapped to more than 1 PID, a PID is essentially a group of SID's.
Not all CID's will be tagged to a PID, but can be individually tagged to a SID which is where table3 comes in to play.
There is no relationship between table1 & 2 to table3.
So essentially tables 1 and 2 say, does this CID belong to a group of SID's, table3 says what other SID's is the CID tagged to.
The view is used by a 3rd party tool to index the CID's against a SID. The SID is passed in as a column from the view, not from the function. The 3rd party index then takes the SID along with the comma seperated list and stores it to say, if a user looks for SID 4946, then they should be able to see all the CID's relating to that SID.
Hope thats a little more clear.
July 27, 2012 at 4:05 am
You are saying that the SID in table2 does not relate to the SID in table3?
What I think I see is that for each PID in table2 you want a comma delimited list of CID's from table3 and table1.
Correct?
July 27, 2012 at 4:07 am
Or, for the SID in table3, you want a distinct comma delimited list of CID's from Table3 and Table1.
July 27, 2012 at 4:19 am
Essentially yes just a comma seperated of CID's from table1 & 3 based on a SID.
The SID in both table2 and table3 will link together as they do map back to the same SID in the Stand table, just from an application perspective its not classed as a relationship.
I'm still trying to get my head around this schema at the moment, was just dumped on me yesterday as we dont usually get involved in the 3rd party side of things, just this view runs against our database and not the 3rd parties we just insert data into the tables it needs as a sperate schema.
July 27, 2012 at 4:27 am
Does the following query meet your requirements?
with BaseData as (
select distinct
[SID]
from
dbo.table3
), CIDData as (
select
bd.[SID],
t3.CID
from
dbo.table3 t3
inner join BaseData bd
on (t3.[SID] = bd.[SID])
union
select
bd.[SID],
t1.CID
from
BaseData bd
inner join dbo.table2 t2
on (bd.[SID] = t2.[SID])
inner join dbo.table1 t1
on (t2.PID = t1.PID)
)
select
bd.[SID],
STUFF((SELECT ', ' + CONVERT(VARCHAR,c.CID)
FROM
CIDData c
inner join BaseData bd
on (c.[SID] = bd.[SID])
ORDER BY c.CID
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,2,'') as CIDs
from
BaseData bd;
July 27, 2012 at 4:38 am
Thanks Lynn I will give that a go.
I just took Cadavre's solution and change it to a iTVF and craeated a test view and it took 13 seconds to run.
I will modify the function to the code you just provided and let you know.
July 27, 2012 at 4:53 am
anthony.green (7/27/2012)
Thanks Lynn I will give that a go.I just took Cadavre's solution and change it to a iTVF and craeated a test view and it took 13 seconds to run.
I will modify the function to the code you just provided and let you know.
Look carefully, this code shouldn't necessarily be converted into a function. It would probably have helped if you had provided the view you are creating that needs this info.
Viewing 15 posts - 16 through 30 (of 56 total)
You must be logged in to reply to this topic. Login to reply