Puzzle – DELETE Qualified Rows From Multiple Tables – Win USD 1000 Worth Class

It has been a while since we have had a puzzle on this blog post, so today we are going to see a very simple but brain teaser puzzle. This puzzle is about Deleting Qualified Rows From Multiple Tables. This question was actually asked to me by my client while working together on the Comprehensive Database Performance Health Check.

Puzzle - DELETE Qualified Rows From Multiple Tables - Win USD 1000 Worth Class del-puz0-800x305

Everyone who gives correct answers to the puzzle will get a 50% discount on SQL Server Performance Tuning Practical Workshop – Recorded Classes and One person will get FREE Access to it as well.

Puzzle Setup – Multiple Tables

The problem which I am going to describe happened with my one of the largest client who is working in the financial technology area (a private bank). However, due to privacy issues, I can’t write here the details about their table structure, I am going to use a sample database of the students and class.

The setup is very simple. We have three tables.

Table 1: Student – Containing student names
Table 2: Class – Containing the class name
Table 3: StudentClass- Containing the relationship between student and class

Here is the script to set up that up:

USE TempDB
GO
-- Set up environment
CREATE TABLE Student (ID INT, StudentName VARCHAR(100))
GO
CREATE TABLE Class (ID INT, ClassName VARCHAR(100))
GO
CREATE TABLE StudentClass (ID INT, ClassID INT, StudentID INT)
GO
-- Populate Data
INSERT INTO Student (ID, StudentName)
SELECT 1, 'Mark'
UNION
SELECT 2, 'John'
UNION
SELECT 3, 'Thomas'
GO
-- Populate Data
INSERT INTO Class (ID, ClassName)
SELECT 1, 'Maths'
UNION
SELECT 2, 'English'
UNION
SELECT 3, 'Science'
GO
-- Populate Data
INSERT INTO StudentClass (ID, ClassID, StudentID)
SELECT 1, 1, 1
UNION
SELECT 2, 2, 2
UNION
SELECT 3, 1, 3
UNION
SELECT 4, 2, 1
UNION
SELECT 5, 2, 3
UNION
SELECT 6, 3, 1
GO
-- Query to List all Students and Classes
SELECT DISTINCT s.StudentName,
		c.ClassName
FROM StudentClass sc
INNER JOIN Class c ON c.ID = sc.ClassID
INNER JOIN Student s ON s.ID = sc.StudentID
GO

Here is the output of the query which we just ran. It lists the name of the students with the class they are enrolled in.

Puzzle - DELETE Qualified Rows From Multiple Tables - Win USD 1000 Worth Class del-puz1

Puzzle Statement – Delete from Multiple Tables

Due to some reason, in the school, the class of English was canceled during the summer. All the students who had enrolled in the English class were affected. Here are two scenarios for us for the students who had taken English class.

Scenario 1: If a student has enrolled in a single class of English – Remove from the Student’s list and Remove from the StudentClass list.
Scenario 2: If a student has enrolled in multiple class with English class – Remove from the StudentClass list.

Puzzle - DELETE Qualified Rows From Multiple Tables - Win USD 1000 Worth Class del-puz2

Here is the initial script was written which satisfies both the scenarios.

SET STATISTICS IO ON 
-- Delete from Students Table
DELETE s
FROM Student s
INNER JOIN StudentClass sc ON s.ID = sc.StudentID 
INNER JOIN Class c ON c.ID = sc.ClassID
WHERE c.ClassName = 'English' AND 
	sc.StudentID IN 
		(SELECT sc2.StudentID 
		FROM StudentClass sc2 
		GROUP BY sc2.StudentID 
		HAVING COUNT(*) = 1)
GO
-- Delete from StudentClass Table
DELETE sc
FROM StudentClass sc
INNER JOIN Class c ON c.ID = sc.ClassID
WHERE c.ClassName = 'English'
GO

If you run the above query, you will get the following output.

Puzzle - DELETE Qualified Rows From Multiple Tables - Win USD 1000 Worth Class del-puz3

 

Additionally, when I go to messages, it shows the following output for multiple tables:

Table ‘Student’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Class’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘StudentClass’. Scan count 7, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row affected)

Table ‘StudentClass’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Class’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(3 rows affected)

Your Task:

Optimize the above DELETE statement. That’s it!

How to Participate

Once you figure out how to optimize the DELETE statement, post your answer in the comments section. Your answer will be invisible until July 20th. I will publish all the answers on July 20th.

I will check the performance of the DELETE statement by running above DELETE statement and your modified suggestion. If your suggestion takes less amount of total logical reads, you will be the winner.

You have complete freedom to do anything to optimize the DELETE statement. Here are a few ideas:

  • Re-write DELETE statement
  • Combine DELETE operations
  • USE CTE
  • USE TempTables
  • Create Indexes
  • Use Subqueries

Pretty much anything you can think of it, you can try. However, your answer should be correct and logical reads should be less. I will also test by passing the different subject in the WHERE clause and will see that your query follows both the scenarios mentioned above.

Everyone Gets to Win

Everyone who gives correct answers to the puzzle will get a 50% discount on SQL Server Performance Tuning Practical Workshop – Recorded Classes and One person will get FREE Access to it as well.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL Puzzle – Correct the Incorrect Query – Win Price Worth USD 1000 – Aggregate and Subquery
Next Post
SQL SERVER – SQL Agent Job and Backslash – Strange Behavior

Related Posts

48 Comments. Leave new

  • SET STATISTICS IO ON

    –select *
    delete sc
    from dbo.StudentClass as sc
    inner join dbo.Class as c
    on sc.ClassID = c.ID
    where c.ClassName = ‘english’

    –select s.*
    delete s
    from dbo.Student as s
    where not exists (select *
    from dbo.StudentClass as sc
    inner join dbo.Class as c
    on sc.ClassID = c.ID
    where sc.StudentID = s.ID
    and c.ClassName ‘english’)

    Reply
  • SET STATISTICS IO ON
    DECLARE @ClassID AS INT
    SELECT @ClassID=ID FROM Class WHERE ClassName=’English’

    DELETE s
    FROM Student s
    INNER JOIN StudentClass sc ON s.ID = sc.StudentID
    WHERE sc.ClassID = @ClassID AND
    sc.StudentID IN
    (SELECT sc2.StudentID
    FROM StudentClass sc2
    GROUP BY sc2.StudentID
    HAVING COUNT(*) = 1)

    DELETE sc
    FROM StudentClass sc
    WHERE sc.ClassID = @ClassID
    GO

    Reply
  • –select *
    delete sc
    from dbo.StudentClass as sc
    inner join dbo.Class as c
    on sc.ClassID = c.ID
    where c.ClassName = ‘english’;

    –select s.*
    delete s
    from dbo.Student as s
    outer apply(select top(1) sc.StudentID
    from dbo.StudentClass as sc
    where sc.StudentID = s.ID
    order by sc.ID
    )oa
    where oa.StudentID is null

    Reply
  • Hi Pinal.
    I’m not sure where it is allowed to use variables and whether we should optimize for general case…. So my version for this particular case:

    create unique clustered index PK_Student on Student(ID);

    declare @id int = (select top 1 ID from Class c WHERE c.ClassName = ‘English’);

    delete s
    from Student s
    where ID in (select StudentID
    from StudentClass
    group by StudentID
    having count(case when ClassID=@id then 1 end)=1)
    option(merge join);

    delete StudentClass where ClassID = @id;

    (5 logical reads for deletes + 1 logical read for create clustered index)

    Reply
  • Calunoth Allerton
    July 15, 2019 2:40 pm

    Here is my solution. I believe by using table variable and OUTPUT clause, I was about to bring the page read to 5 page reads only.

    — Declare Variable for @ClassID
    DECLARE @ClassID AS INT
    SELECT @ClassID=ID
    FROM Class WHERE
    ClassName=’English’;

    — Delete from StudentClass Table
    DELETE sc
    FROM StudentClass sc
    WHERE sc.ClassID = @ClassID;

    — Delete from Students Table
    DELETE s
    FROM dbo.Student s
    WHERE NOT EXISTS (SELECT StudentID
    FROM StudentClass sc WHERE sc.StudentID = s.ID)

    Here is the output I receive where it says there are only 6 logical reads.

    Table ‘Class’. Scan count 1, logical reads 1,
    Table ‘StudentClass’. Scan count 1, logical reads 1

    Table ‘Student’. Scan count 1, logical reads 1
    Table ‘StudentClass’. Scan count 1, logical reads 3

    Btw, I have already attended your class 3 years ago in the UK if you remember me. (Hint: You pronounced my name wrong till the end of the class) I used the two techniques which you had taught in the class in this solution. I hope that I can win this puzzle. If I do not win, I will still purchase the recorded class.

    Reply
    • Hi Calunoth,

      I do remember you – great solution indeed.

      I am happy that you remember the improve the query performance.

      Reply
  • SET STATISTICS IO OFF

    USE TempDB
    GO
    — Set up environment
    SET NOCOUNT ON
    GO

    DROP TABLE IF EXISTS Student
    CREATE TABLE Student (ID INT, StudentName VARCHAR(100))
    GO
    DROP TABLE IF EXISTS Class
    CREATE TABLE Class (ID INT , ClassName VARCHAR(100))
    GO
    DROP TABLE IF EXISTS StudentClass
    CREATE TABLE StudentClass (ID INT, ClassID INT, StudentID INT)
    GO
    — Populate Data
    INSERT INTO Student (ID, StudentName)
    SELECT 1, ‘Mark’
    UNION
    SELECT 2, ‘John’
    UNION
    SELECT 3, ‘Thomas’
    GO
    — Populate Data
    INSERT INTO Class (ID, ClassName)
    SELECT 1, ‘Maths’
    UNION
    SELECT 2, ‘English’
    UNION
    SELECT 3, ‘Science’
    GO
    — Populate Data
    INSERT INTO StudentClass (ID, ClassID, StudentID)
    SELECT 1, 1, 1
    UNION
    SELECT 2, 2, 2
    UNION
    SELECT 3, 1, 3
    UNION
    SELECT 4, 2, 1
    UNION
    SELECT 5, 2, 3
    UNION
    SELECT 6, 3, 1
    GO
    — Query to List all Students and Classes
    SELECT DISTINCT s.StudentName,
    c.ClassName
    FROM StudentClass sc
    INNER JOIN Class c ON c.ID = sc.ClassID
    INNER JOIN Student s ON s.ID = sc.StudentID
    GO

    /* My solution */

    SET STATISTICS IO ON

    — Class ID we want to get rid of
    DECLARE @CLASSid integer
    SELECT @CLASSid = ID
    FROM Class
    WHERE ClassName = ‘English’

    — Delete from StudentClass Table
    DELETE sc
    FROM StudentClass sc
    WHERE SC.ClassID = @CLASSid

    — Delete any students that now dont have any class to go to — could also use not exists
    DELETE FROM Student
    WHERE ID NOT IN (
    SELECT StudentID
    FROM StudentClass
    )

    /* 6 LOGICAL READS */

    GO

    SET STATISTICS IO OFF
    go

    SELECT DISTINCT s.StudentName,
    c.ClassName
    FROM StudentClass sc
    INNER JOIN Class c ON c.ID = sc.ClassID
    INNER JOIN Student s ON s.ID = sc.StudentID

    Reply
  • 1) Create Foreign key relationship with “On Cascade Delete”. So ID in the student table, will reference the StudentID in StudentClass Table.
    2) While executing delete statement on Student table, it will automatically deletes the record on StudentClass table also

    Reply
  • Sanjay Monpara
    July 15, 2019 6:15 pm

    SET STATISTICS IO ON;

    — Delete from StudentClass Table
    DELETE sc
    FROM StudentClass sc
    INNER JOIN Class c ON c.ID = sc.ClassID
    WHERE c.ClassName = ‘English’;
    GO

    — Delete from Students Table
    DELETE FROM dbo.Student
    WHERE ID NOT IN (SELECT StudentID
    FROM dbo.StudentClass);

    Reply
  • SET STATISTICS IO ON
    Select s.ID,S.StudentID INto #temp from
    StudentClass s
    Where StudentID In(Select StudentID from StudentClass Group BY StudentID Having Count(ClassID) = 1)
    AND EXISTS(Select ID from Class c Where c.ID=s.classID AND c.ClassName=’ENglish’)

    — Delete from StudentClass Table
    Delete From StudentClass
    Where ID IN (Select ID from #temp)

    — Delete from Students Table
    Delete From Student
    Where ID IN (Select StudentID from #temp)
    DROP Table #temp

    Select s.ID,S.StudentID INto #temp1 from
    StudentClass s
    Where StudentID In(Select StudentID from StudentClass Group BY StudentID Having Count(ClassID) > 1)
    AND EXISTS(Select ID from Class c Where c.ID=s.classID AND c.ClassName=’ENglish’)

    — Delete from StudentClass Table
    Delete From StudentClass
    Where ID IN (Select ID from #temp1)

    — Delete from Students Table
    Delete From Student
    Where ID IN (Select StudentID from #temp1)
    DROP Table #temp1

    Reply
    • A great attempt but the logical reads are way higher compared to original my logical reads. In your case, it is 39 total logical reads.

      Reply
    • Additionally, it deletes everything from StudentClass table.

      Reply
  • USE tempdb;

    — Bryan Rebok solution for https://blog.sqlauthority.com/2019/07/13/puzzle-delete-qualified-rows-from-multiple-tables-win-usd-1000-worth-class/

    SET STATISTICS IO ON;

    — as an alternative, using a HASH JOIN hint brings it down to 9 logical reads total
    –DELETE sc
    –FROM StudentClass sc
    –INNER HASH JOIN Class c
    — ON c.ID = sc.ClassID
    –WHERE c.ClassName = ‘English’;
    –GO

    — Delete from StudentClass Table
    DELETE sc
    FROM StudentClass sc
    WHERE EXISTS

    (SELECT ‘X’
    FROM Class c
    WHERE c.ID = sc.ClassID
    AND c.ClassName = ‘English’);
    GO

    — Delete from Students Table
    DELETE s
    FROM Student s
    WHERE NOT EXISTS

    (SELECT ‘X’
    FROM StudentClass sc
    WHERE s.ID = sc.StudentID);
    GO

    /*
    SELECT *
    FROM Student;

    SELECT *
    FROM Class;

    SELECT *
    FROM StudentClass;
    */

    Reply
  • Hi:

    I will able to achieve that by using cursor

    Create proc DeleteStudent @className nvarchar(max)
    as
    Begin
    Declare @ClassId int
    select @classId=c.ID from Class as c where c.className=@className

    DECLARE Details_Cursor CURSOR FAST_FORWARD LOCAL FOR
    select studentid from studentclass as sc where sc.ClassID=@classId
    declare @studentId int,@stdCount int
    OPEN Details2_Cursor
    fetch next from Details_Cursor into @studentId
    while @@FETCH_STATUS=0
    BEGIN
    select @stdCount=COUNT(*) from studentclass where studentid= @studentId and classid!=@classId
    if(@stdCount=0)
    Begin
    delete from studentclass where studentid= @studentId and classid=@classId
    delete from student where id= @studentId
    End
    Else
    Begin
    delete from studentclass where studentid= @studentId and classid=@classId
    End
    delete from class where classid=@ClassId
    fetch next from Details_Cursor into @studentId
    END
    CLOSE Details_Cursor
    DEALLOCATE Details_Cursor
    end

    exec DeleteStudent ‘english’

    Reply
  • parimal patel
    July 19, 2019 11:58 am

    ;WITH CTE1 AS
    (
    select * FROM StudentClass
    ),CTE2 AS
    (
    select * FROM Class where ClassName = ‘English’
    ),CTE3 AS
    (
    select * FROM Student
    )delete from Student where id in (select CTE1.id
    –select top 1 CTE1.id
    from CTE1 inner join CTE2 on CTE1.id= CTE2.ID
    inner join CTE3 on CTE1.id= CTE2.ID

    ;WITH CTE1 AS
    (
    select * FROM StudentClass
    ),CTE2 AS
    (
    select * FROM Class where ClassName = ‘English’
    ),CTE3 AS
    (
    select * FROM Student
    )delete from StudentClass where id in (select CTE2.id
    from CTE1 inner join CTE2 on CTE1.id= CTE2.ID
    inner join CTE3 on CTE1.id= CTE2.ID)

    Reply
    • Ia m not able to run as it keep on giving me some error.

      Reply
      • ;WITH CTE1 AS
        (
        select * FROM StudentClass
        ),CTE2 AS
        (
        select * FROM Class where ClassName = ‘English’
        ),CTE3 AS
        (
        select * FROM Student
        )delete from Student where id in (select CTE1.id
        –select top 1 CTE1.id
        from CTE1 inner join CTE2 on CTE1.id= CTE2.ID
        inner join CTE3 on CTE1.id= CTE2.ID ) — ONE BRACKET WAS MISSING

        ;WITH CTE1 AS
        (
        select * FROM StudentClass
        ),CTE2 AS
        (
        select * FROM Class where ClassName = ‘English’
        ),CTE3 AS
        (
        select * FROM Student
        )DELETE from StudentClass where id in (select CTE2.id
        from CTE1 inner join CTE2 on CTE1.id= CTE2.ID
        inner join CTE3 on CTE1.id= CTE2.ID)

      • Hi The contest is over.

  • parimal patel
    July 19, 2019 12:00 pm

    Hi Sir, i
    this is just 1 try without indexing

    Reply
  • –option 1 -use table variable

    declare @del_Stud TABLE (ID INT)

    INSERT INTO @del_Stud
    select s.ID FROM Student s
    INNER JOIN (select StudentID,MAX(ClassID) ClassID from StudentClass sc group by StudentID having count(*)=1)t ON s.ID = t.StudentID
    INNER JOIN Class c ON c.ID = t.ClassID AND c.ClassName=’English’

    select * from @del_Stud

    delete from Student where id in (select ID from @del_Stud)
    delete from StudentClass where id in (select ID from @del_Stud)

    –option 2 –using student to delete stud class

    DELETE s
    FROM Student s
    INNER JOIN (select StudentID,MAX(ClassID) ClassID from StudentClass sc group by StudentID having count(*)=1)t ON s.ID = t.StudentID
    INNER JOIN Class c ON c.ID = t.ClassID AND c.ClassName=’English’

    DELETE sc
    FROM StudentClass sc LEFT OUTER JOIN Student s ON s.ID=sc.StudentID
    WHERE s.StudentName is null

    Reply
  • Nitin Dhiman
    July 19, 2019 4:39 pm

    DELETE sc FROM StudentClass sc
    INNER JOIN class c ON c.ID=sc.ClassID
    WHERE c.ClassName=’English’

    DELETE FROM Student WHERE ID NOT IN
    (SELECT DISTINCT StudentID FROM StudentClass )

    Reply
  • Second solution from my side:

    SET STATISTICS IO ON
    DECLARE @ClassID AS INT
    SELECT @ClassID=ID FROM Class WHERE ClassName=’English’

    DECLARE @StudentID AS VARCHAR(MAX)
    SELECT @StudentID= COALESCE(@StudentID , ‘,’) + CAST(StudentID AS VARCHAR(20)) + ‘,’
    FROM StudentClass
    GROUP BY StudentID
    HAVING COUNT(*) = 1 AND MIN(ClassID)=@ClassID

    DELETE FROM Student WHERE @StudentID LIKE ‘%,’ + CAST(ID AS VARCHAR(20)) + ‘,%’
    DELETE FROM StudentClass WHERE ClassID=@ClassID
    GO

    Reply
  • When/Where are the answers going to published?

    Reply
  • Hi All,

    The comment of Harsh is the winner. He gets access to the FREE class worth USD 1000.

    His solution is excellent!

    https://blog.sqlauthority.com/2019/07/13/puzzle-delete-qualified-rows-from-multiple-tables-win-usd-1000-worth-class/comment-page-1/#comment-2037848

    Reply
  • Everyone who has answered question on this blog post gets 50% of the https://blog.sqlauthority.com/sql-server-performance-tuning-practical-workshop-recorded-classes/

    Please reach out to me.

    Reply
  • Bit late for the Quiz participation . But below script gives the logical read of 2
    SET STATISTICS IO ON;

    DEClare @classID INT
    SELECT @classID=id from class with(nolock) WHERE className=’English’

    DELETE FROM studentclass where classID= @ClassID

    delete S
    FROM student S
    LEFT JOIN StudentClass SC WITH(NOLOCK) ON SC.StudentID=S.id
    WHERE SC.StudentID IS NULL AND SC.classID = @classID

    Table ‘Class’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table ‘StudentClass’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (3 rows affected)
    Table ‘Student’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (0 rows affected)

    Reply

Leave a Reply

Menu