July 27, 2012 at 5:12 am
This is the view, currently obfuscating the tables and generating some sample data which will follow if needs be.
selects.SID,
s.Label,
s.State,
clAge.ClassID AS AgeGradeID,
clSubject.ClassID AS SubjectID,
s.Description,
dbo.functioncall(s.SID) AS ResourceList
from Stand s
join AgeGrade ssAge ON ssAge.AGID = s.AGID
join Class clAge ON clAge.ParentID = '002' and clAge.ClassID = ssAge.ParentClassID
join StandGroupsClass ccmap ON ccmap.PID = s.SID
join Class clSubject ON clSubject.ParentID = '003' and clSubject.ClassID = ccmap.ClassID
where
s.active = 1
and
(
s.SID in
(
select SID
from ContStand
) or
s.SID IN
(
select distinct map.SID
from ContStandGroup csg
JOIN StandGroupMap map
ON map.PID = csg.PID
)
)
GO
July 27, 2012 at 6:17 am
You've probably cracked this now Anthony, with Lynn's code.
You could do this with a QU on the output but you'd have a new column with an incrementing string until the last row of the partition (SID) and something tells me this would be quite inefficient. I had a play with MERGE because I've known for ages that it supports [variable = column = expression] etc as used by the QU, and interestingly you can set it up so that it doesn't even update a column in the target table. Then I wondered if the same would happen with a "normal" QU and of course it does, and quite quickly too - 7ms per 383 rows.
I'm not sure if or how it might meet your requirement but here it is:
SET NOCOUNT ON;
DROP TABLE #Temp
SELECT SID, CID
INTO #Temp
FROM (
SELECT map.SID, csg.CID
FROM table1 csg
INNER JOIN table2 map ON map.PID = csg.PID
UNION
SELECT cs.SID, cs.CID
FROM table3 cs
) d; -- 383 rows for SID = 4946
CREATE UNIQUE CLUSTERED INDEX ucx_Temp ON #Temp (SID, CID);
--=======
DECLARE @LongCIDs TABLE ([SID] INT, LongCID VARCHAR(8000));
DECLARE @sid INT, @LongCID VARCHAR(8000);
-- Loop START (aaarrgghhhhh!!) through values of SID
SELECT @sid = 4946, @LongCID = '' -- increment through SID, reinit @LongCID
/*
-- notice the MERGE isn't updating a column, only a variable.
-- and it's SUPPORTED. [@variable = expression]
MERGE #Temp AS t USING (SELECT [SID] = @sid) AS s ON t.[SID] = s.[SID]
WHEN MATCHED THEN UPDATE SET @LongCID = @LongCID + ',' + CAST(t.CID AS VARCHAR(10));
-- CPU time = 0 ms, elapsed time = 47 ms / SID
*/
UPDATE #Temp SET @LongCID = @LongCID + ',' + CAST(#Temp.CID AS VARCHAR(10)) WHERE [SID] = @sid;
-- CPU time = 0 ms, elapsed time = 7 ms / SID
INSERT INTO @LongCIDs VALUES (@SID, @LongCID);
-- Loop END
SELECT * FROM @LongCIDs;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 27, 2012 at 6:20 am
anthony.green (7/27/2012)
This is the view, currently obfuscating the tables and generating some sample data which will follow if needs be.
selects.SID,
s.Label,
s.State,
clAge.ClassID AS AgeGradeID,
clSubject.ClassID AS SubjectID,
s.Description,
dbo.functioncall(s.SID) AS ResourceList
from Stand s
join AgeGrade ssAge ON ssAge.AGID = s.AGID
join Class clAge ON clAge.ParentID = '002' and clAge.ClassID = ssAge.ParentClassID
join StandGroupsClass ccmap ON ccmap.PID = s.SID
join Class clSubject ON clSubject.ParentID = '003' and clSubject.ClassID = ccmap.ClassID
where
s.active = 1
and
(
s.SID in
(
select SID
from ContStand
) or
s.SID IN
(
select distinct map.SID
from ContStandGroup csg
JOIN StandGroupMap map
ON map.PID = csg.PID
)
)
GO
This:
(
select SID
from ContStand
) or
s.SID IN
(
select distinct map.SID
from ContStandGroup csg
JOIN StandGroupMap map
ON map.PID = csg.PID
)
looks like the SID part of what I provide in my query. Is this true?
July 27, 2012 at 6:56 am
Does this do what you are trying to accomplish?
This code won't work, the CTE is declared in the wrong place. Corrected code is in the next post.
selects.SID,
s.Label,
s.State,
clAge.ClassID AS AgeGradeID,
clSubject.ClassID AS SubjectID,
s.Description,
dt.CIDs --dbo.functioncall(s.SID) AS ResourceList
from Stand s
join AgeGrade ssAge ON ssAge.AGID = s.AGID
join Class clAge ON clAge.ParentID = '002' and clAge.ClassID = ssAge.ParentClassID
join StandGroupsClass ccmap ON ccmap.PID = s.SID
join Class clSubject ON clSubject.ParentID = '003' and clSubject.ClassID = ccmap.ClassID
inner join (
with BaseData as (
select
[SID]
from
dbo.ContStand -- table3
union
select
[SID]
from
dbo.StandGroupMap -- table2
), CIDData as (
select
bd.[SID],
t3.CID
from
dbo.ContStand t3 -- table3
inner join BaseData bd
on (t3.[SID] = bd.[SID])
union
select
bd.[SID],
t1.CID
from
BaseData bd
inner join dbo.StandGroupMap t2 -- table2
on (bd.[SID] = t2.[SID])
inner join dbo.ContStandGroup t1 -- table1
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
) dt([SID],CIDs)
on s.[SID] = dt.[SID]
where
s.active = 1
and
(
s.SID in
(
select SID
from ContStand
) or
s.SID IN
(
select distinct map.SID
from ContStandGroup csg
JOIN StandGroupMap map
ON map.PID = csg.PID
)
)
July 27, 2012 at 7:04 am
Lynn Pettis (7/27/2012)
Does this do what you are trying to accomplish?
with BaseData as (
select
[SID]
from
dbo.ContStand -- table3
union
select
[SID]
from
dbo.StandGroupMap -- table2
), CIDData as (
select
bd.[SID],
t3.CID
from
dbo.ContStand t3 -- table3
inner join BaseData bd
on (t3.[SID] = bd.[SID])
union
select
bd.[SID],
t1.CID
from
BaseData bd
inner join dbo.StandGroupMap t2 -- table2
on (bd.[SID] = t2.[SID])
inner join dbo.ContStandGroup t1 -- table1
on (t2.PID = t1.PID)
)
selects.SID,
s.Label,
s.State,
clAge.ClassID AS AgeGradeID,
clSubject.ClassID AS SubjectID,
s.Description,
dt.CIDs --dbo.functioncall(s.SID) AS ResourceList
from Stand s
join AgeGrade ssAge ON ssAge.AGID = s.AGID
join Class clAge ON clAge.ParentID = '002' and clAge.ClassID = ssAge.ParentClassID
join StandGroupsClass ccmap ON ccmap.PID = s.SID
join Class clSubject ON clSubject.ParentID = '003' and clSubject.ClassID = ccmap.ClassID
inner join (
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
) dt([SID],CIDs)
on s.[SID] = dt.[SID]
where
s.active = 1
and
(
s.SID in
(
select SID
from ContStand
) or
s.SID IN
(
select distinct map.SID
from ContStandGroup csg
JOIN StandGroupMap map
ON map.PID = csg.PID
)
)
July 27, 2012 at 7:08 am
The AND criteria in the WHERE clause could possibily go away if the last join I added is correct.
July 27, 2012 at 11:47 am
OR should that INNER JOIN be a CROSS APPLY? Could sleep last night and with nothing to use for testing I may have the wrong statement there. Thinking about it, the CROSS APPLY may be the correct one to use.
Even so, it works as an INNER JOIN and the AND part of the WHERE clause could still possibly go away.
July 29, 2012 at 11:40 am
anthony.green (7/27/2012)
This is the view, currently obfuscating the tables and generating some sample data which will follow if needs be.
selects.SID,
s.Label,
s.State,
clAge.ClassID AS AgeGradeID,
clSubject.ClassID AS SubjectID,
s.Description,
dbo.functioncall(s.SID) AS ResourceList
from Stand s
join AgeGrade ssAge ON ssAge.AGID = s.AGID
join Class clAge ON clAge.ParentID = '002' and clAge.ClassID = ssAge.ParentClassID
join StandGroupsClass ccmap ON ccmap.PID = s.SID
join Class clSubject ON clSubject.ParentID = '003' and clSubject.ClassID = ccmap.ClassID
where
s.active = 1
and
(
s.SID in
(
select SID
from ContStand
) or
s.SID IN
(
select distinct map.SID
from ContStandGroup csg
JOIN StandGroupMap map
ON map.PID = csg.PID
)
)
GO
You can probably get a sometimes significant bit more performance out of this by getting rid of the DISTINCT. "IN" inherently does a DISTINCT without having to do a sort to do it.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 1, 2012 at 6:20 am
Sorry all, went on holiday, just back on with this now.
I have created a database and backed it up which you can download from here
http://antg.dyndns-home.com:8080/myweb/other/joiner.bak.
It contains the tables with sample data which is obfuscated, the view and the current live function using STUFF.
You can run the view and it returns 1 row so you can get the expected results.
Lynn - Many thanks for that solution, while it works its doesnt work at the same time as it just builds up the same list of CIDs for each SID.
August 1, 2012 at 7:20 am
anthony.green (8/1/2012)
Sorry all, went on holiday, just back on with this now.I have created a database and backed it up which you can download from here
http://antg.dyndns-home.com:8080/myweb/other/joiner.bak.
It contains the tables with sample data which is obfuscated, the view and the current live function using STUFF.
You can run the view and it returns 1 row so you can get the expected results.
Lynn - Many thanks for that solution, while it works its doesnt work at the same time as it just builds up the same list of CIDs for each SID.
I'm not sure what you mean or which solution you are talking about. I posted one that used the three tables you provided and then I posted one where I tried to integrate that solution into your larger query in place of your function call.
August 1, 2012 at 7:25 am
This function runs about 25% faster:
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER FUNCTION [dbo].[GetCIDs]
(@SID INT)
RETURNS @Result TABLE (LongCID VARCHAR(8000))
AS
BEGIN
DECLARE @Accumulator TABLE (seq INT, CID INT);
INSERT INTO @Accumulator (seq, CID)
SELECT seq = ROW_NUMBER() OVER(ORDER BY CID), CID
FROM (
SELECT csg.CID
FROM ContentStandardsGroup csg
JOIN StandardsGroupMapping map
ON map.PID = csg.PID
WHERE map.SID = @sid
UNION
SELECT cs.CID
FROM ContentStandard cs
WHERE cs.SID = @sid
) d; -- 383 rows for SID = 4946
DECLARE @LongCID VARCHAR(8000); SELECT @LongCID = '';
UPDATE @Accumulator SET @LongCID = @LongCID + ',' + CAST(CID AS VARCHAR(10));
INSERT INTO @Result SELECT @LongCID;
RETURN
END
With a small modification to the view:
CREATE VIEW [dbo].[View2] AS
select
s.SID,
s.Label,
s.State,
clAge.ClassID AS AgeGradeID,
clSubject.ClassID AS SubjectID,
s.Description,
Func.LongCID AS ResourceList
from Standard s
join StandardSetAgeGrade ssAge
ON ssAge.AGID = s.AGID
join Classification clAge
ON clAge.ParentID = '002'
and clAge.ClassID = ssAge.ParentClassID
join StandardsGroupsClassification ccmap
ON ccmap.PID = s.SID
join Classification clSubject
ON clSubject.ParentID = '003'
and clSubject.ClassID = ccmap.ClassID
cross apply dbo.[getCIDs] (s.SID) AS Func
where s.active = 1
and
(
s.SID in (select SID from Contentstandard)
or s.SID IN
(select map.SID
from ContentStandardsGroup csg
JOIN StandardsGroupMapping map
ON map.PID = csg.PID
)
)
GO
I think there's a more performant way of achieving this...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2012 at 7:57 am
There is a mistake in the view in the database, got a bit delete happy with obfuscating the view.
The line
join StandardsGroupsClassification ccmap
ON ccmap.PID = s.SID
should be
join StandardsGroupsClassification ccmap
ON ccmap.PID = s.SubID
Chris, I have just tested your function and modified view and it is considerably slower then the STUFF with XML PATH function.
Lynn, sorry I was refering to the integration to the larger query
August 1, 2012 at 8:03 am
How did you time the runs, Anthony?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2012 at 8:05 am
anthony.green (8/1/2012)
There is a mistake in the view in the database, got a bit delete happy with obfuscating the view.The line
join StandardsGroupsClassification ccmap
ON ccmap.PID = s.SID
should be
join StandardsGroupsClassification ccmap
ON ccmap.PID = s.SubID
Chris, I have just tested your function and modified view and it is considerably slower then the STUFF with XML PATH function.
Lynn, sorry I was refering to the integration to the larger query
Okay, not as concerned as that was a shot in the dark any ways. How accurate was the mapping from your table1, table2, table3 to what was in the view? Did I miss anything?
August 1, 2012 at 8:41 am
Chris,
Just a simple execute the two views, the original came back in 12 seconds, yours was over 2 minutes.
Lynn,
No you where right on the mappings
Now to throw another curve ball into the mix, apparently the view has been developed wrong as its doing extra joins which it shouldnt need to, so factoring out the extra work the view now looks like the below
WITH SIDs AS
(
SELECT
SID
FROM
ContentStandard
GROUP BY SID
UNION
SELECT
SID
FROM
ContentStandardsGroup CSG
INNER JOIN
StandardsGroupMapping SGM
ON
SGM.PID = CSG.PID
)
SELECT
s.SID,
s.Label,
s.State,
clAge.ClassID AS AgeGradeID,
s.SubID,
s.Description,
FUNC.CommaList AS ResourceList
FROM
dbo.Standard AS s
INNER JOIN
dbo.StandardSetAgeGrade AS ssAge ON ssAge.AGID = s.AGID
INNER JOIN
dbo.Classification AS clAge ON clAge.ParentID = '002' AND clAge.ClassID = ssAge.ParentClassID
INNER JOIN
SIDs T1 ON S.SID = T1.SID
CROSS APPLY
dbo.getCIDsForSID_NEW(s.SID) AS FUNC
WHERE
s.Active = 1
Viewing 15 posts - 31 through 45 (of 56 total)
You must be logged in to reply to this topic. Login to reply