question

Hello Techie, I have two tables with a highly denormalized nature, Master and child table. records In table: 40 million For a given specific line there is some valid footnote, and I need to check in child table whether a child table contain only those footnote for the line which is present in Master. If not than that record need to be in output **i tried to do like given below** but for small resultset this is giving output correctly, but for table have million of records even after 4 hours query is still executing. **may someone please share your expertise.or some other way to achieve this** Table and data DECLARE @MASTER TABLE ( Footnote VARCHAR (50), Line VARCHAR (50) ) INSERT @MASTER SELECT 'O031', 'EXHAUSTMUF' UNION ALL SELECT 'V049', 'CAMSHAFT' UNION ALL SELECT 'V049', 'DSHBRDCOVR' UNION ALL SELECT 'V049', 'EXHAUSTMUF' UNION ALL SELECT 'O040', 'EXHAUSTMUF' UNION ALL SELECT 'V133', 'DSHBRDCOVR' UNION ALL SELECT 'V133', 'EXHAUSTMUF' UNION ALL SELECT 'E014', 'CAMSHAFT' UNION ALL SELECT 'E014', 'EXHAUSTMUF' UNION ALL SELECT 'O062', 'EXHAUSTMUF' UNION ALL SELECT 'O194', 'EXHAUSTMUF' DECLARE @ChildTable TABLE ( line VARCHAR (50), footnote_list VARCHAR (50) ) INSERT @ChildTable select 'EXHAUSTMUF', 'O031, V049' UNION ALL select 'EXHAUSTMUF' ,'O040, V133' UNION ALL select 'EXHAUSTMUF', 'E014, O062, O194' UNION ALL select 'DSHBRDCOVR', 'USP2,LTS9' UNION ALL select 'DSHBRDCOVR', 'V049' Output **DSHBRDCOVR USP2,LTS9** I tried to do something like this. First added one function: create FUNCTION [dbo].[fnSplitString]( @string NVARCHAR(MAX), @delimiter CHAR(1), @keylist nvarchar(50)) RETURNS @output TABLE(splitdata NVARCHAR(MAX),keyName nvarchar(50)) BEGIN DECLARE @start INT, @end INT SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) WHILE @start < LEN(@string) + 1 BEGIN IF @end = 0 SET @end = LEN(@string) + 1 INSERT INTO @output (splitdata,keyName) VALUES(ltrim(rtrim( SUBSTRING(@string, @start, @end - @start))),@keylist) SET @start = @end + 1 SET @end = CHARINDEX(@delimiter, @string, @start) END RETURN END --storing splitted values DECLARE @SplittedTable TABLE ( splitted VARCHAR (50), keys VARCHAR (50) ) --getting max rows declare @maxRows int =(SELECT count(*) FROM @childtable) --loop over each rows WHILE(@maxRows!=0) BEGIN DECLARE @footNotList nvarchar(max)=( SELECT abc.footnote_list FROM (SELECT ct.footnote_list, ROW_NUMBER() OVER (ORDER BY ct.footnote_list DESC) AS RowNumber FROM @ChildTable AS ct ) abc WHERE abc.RowNumber=@maxRows ) --inserting into splitted table INSERT INTO @SplittedTable SELECT * FROM dbo.fnSplitString(@footNotList,',',@footNotList) set @maxRows=@maxRows-1; END --selecting by keys and finding them into master SELECT * FROM @ChildTable ct WHERE ct.footnote_list IN ( SELECT DISTINCT st.keys FROM @SplittedTable AS st WHERE st.splitted NOT IN (SELECT Footnote FROM @MASTER m )) Thanks