0
\$\begingroup\$

Goal: make this code as efficient as possible.

Given a finite dataset (Colors - this isn't Crayola here :D) how do I know what colors are not assigned to a given user?

Additional requirement - the final result should output a single column of comma-delimited colors. Spaces do not matter, but it'd be nice.

This current code chokes after 5,000 records and when this scales it will easily surpass 500,000.

USE master
GO

--======================================================================
-- Data Setup.
--======================================================================
IF OBJECT_ID('tempdb..#Employees') IS NOT NULL
BEGIN
    DROP TABLE #Employees
END

CREATE TABLE #Employees(
    ID INT
    ,Name VARCHAR(MAX)
    ,FavoriteColor VARCHAR(MAX)
)

IF OBJECT_ID('tempdb..#Colors') IS NOT NULL
BEGIN
    DROP TABLE #Colors
END

CREATE TABLE #Colors( Color VARCHAR(MAX))

INSERT INTO #Colors VALUES('Blue');
INSERT INTO #Colors VALUES('Green');
INSERT INTO #Colors VALUES('Red');
INSERT INTO #Colors VALUES('Yellow');
INSERT INTO #Colors VALUES('Orange');
INSERT INTO #Colors VALUES('White');
INSERT INTO #Colors VALUES('Black');
INSERT INTO #Colors VALUES('Cyan');
INSERT INTO #Colors VALUES('Magenta');
INSERT INTO #Colors VALUES('Brown');

INSERT INTO #Employees VALUES(1, 'Bob', 'Blue');
INSERT INTO #Employees VALUES(1, 'Bob', 'Green');
INSERT INTO #Employees VALUES(1, 'Bob', 'Red');
INSERT INTO #Employees VALUES(1, 'Bob', 'Yellow');
INSERT INTO #Employees VALUES(2, 'Kate', 'White');
INSERT INTO #Employees VALUES(3, 'Ben', 'Yellow');
INSERT INTO #Employees VALUES(3, 'Ben', 'Magenta');
INSERT INTO #Employees VALUES(3, 'Ben', 'Cyan');

--======================================================================
--Select the total number of favorite colors for each employee. - Done
--======================================================================
SELECT ID, Name, COUNT(FavoriteColor) AS FavoriteColorCount
FROM #Employees
GROUP BY ID, Name

--======================================================================
--Select each employee's favorite colors. - Done
--======================================================================
SELECT ID, Name, COUNT(FavoriteColor) AS FavoriteColorCount
,STUFF(ISNULL((SELECT ', ' + CONVERT(VARCHAR(10), x.FavoriteColor)
                FROM #Employees x
               WHERE     E.ID = x.ID AND E.Name = X.Name
            GROUP BY x.FavoriteColor FOR XML PATH (''), TYPE).value('.','VARCHAR(max)'), ''), 1, 2, '') AS FavoriteColorsList   
FROM #Employees E
GROUP BY ID, Name

--======================================================================
--Select colors not in the favorite employee's list. - Having issues
--======================================================================
SELECT ID, Name, COUNT(FavoriteColor) AS FavoriteColorCount
,STUFF(ISNULL((SELECT ', ' + CONVERT(VARCHAR(10), x.FavoriteColor)
                FROM #Employees x
               WHERE     E.ID = x.ID AND E.Name = X.Name
            GROUP BY x.FavoriteColor FOR XML PATH (''), TYPE).value('.','VARCHAR(max)'), ''), 1, 2, '') AS FavoriteColorsList   
, '' AS ExcludedColorsList  --How do I get this value?
FROM #Employees E
GROUP BY ID, Name

--======================================================================
--CURRENT IMPLEMENTATION - very slow, having performance problems.
--======================================================================
IF OBJECT_ID('tempdb..#Exploded') IS NOT NULL
BEGIN
    DROP TABLE #Exploded
END

--Explode the dataset and isolate all records not included in the users selection.
SELECT DISTINCT 
     E.ID
    ,E.Name
    ,C.Color
INTO #Exploded
FROM #Employees E
CROSS JOIN #Colors C
EXCEPT 
SELECT 
     E.ID
    ,E.Name
    ,E.FavoriteColor
FROM #Employees E

SELECT DISTINCT Ex.ID, Ex.Name
,STUFF((
         select ',' + CONVERT(VARCHAR(10), T1.Color)
        from #Exploded T1
        where   T1.ID       = Ex.ID
            AND T1.Name = Ex.Name

        for xml path(''), type
    ).value('.', 'varchar(max)'), 1, 1, '') AS MissingColorList

FROM #Exploded Ex
INNER JOIN #Employees EMP ON 
    Ex.ID = Emp.ID
    AND Ex.Name = EMP.Name
\$\endgroup\$
1
\$\begingroup\$

First of all you should normalize your data. It is N-M relationship and you should use junction table:

CREATE TABLE Employees(ID INT IDENTITY(1,1) PRIMARY KEY,
                       Name VARCHAR(100));

CREATE TABLE Colors(ID INT PRIMARY KEY,
                    Color VARCHAR(100),
                    UNIQUE(Color));

CREATE TABLE EmployeesColors(EmployeeID INT REFERENCES Employees(ID),
                             ColorId INT REFERENCES Colors(ID),
                             PRIMARY KEY (EmployeeID, ColorID));

Then your query could be simplified to:

;WITH cte AS
(
  SELECT sub.EmpId, sub.Name, sub.ColorID, sub.Color
  FROM (SELECT EmpId = e.ID, e.Name, ColorId = c.ID, c.Color
        FROM #Employees e
        CROSS JOIN #Colors c) AS sub
  LEFT JOIN #EmployeesColors ec
    ON sub.EmpId = ec.EmployeeID
   AND sub.ColorID = ec.ColorID
  WHERE ec.EmployeeID IS NULL
)
SELECT DISTINCT ID = EmpId, Name
      ,MissingColors = STUFF((SELECT ',' + T2.Color      
                              FROM cte T2
                              WHERE T1.EmpId = T2.EmpId  
                              FOR XML PATH(''), TYPE
                            ).value('.', 'varchar(max)'), 1, 1, '')
FROM cte T1
--ORDER BY ID;

LiveDemo

\$\endgroup\$
  • \$\begingroup\$ On the right track. This is better than what I have originally, however. This tends to choke on more than 150 colors. Example \$\endgroup\$ – Mr. C Feb 10 '16 at 18:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.