Fewest Logical Reads: The Answer to the Delete Qualified Rows Puzzle

The fewest logical reads come from reading each table as few times as the rules allow. The best answer measured here needs 4 logical reads. The starting script needed 19. A set based version needs 5, and it is the one to use. Both are below, with the tests.

The Puzzle in Short

The puzzle is in Delete Qualified Rows From Multiple Tables: A SQL Puzzle. The English class is cancelled. A student whose only class was English leaves the student list, and the enrollment goes with them. A student with other classes stays, and only the English enrollment goes. A student has at most one row for each class. The score is the total of logical reads.

This script creates a database named StudentClassSolutionDemo. It holds the three tables from the puzzle and a procedure that puts the data back.

IF DB_ID(N'StudentClassSolutionDemo') IS NULL CREATE DATABASE StudentClassSolutionDemo;
GO
USE StudentClassSolutionDemo;
GO
DROP TABLE IF EXISTS dbo.StudentClass, dbo.Class, dbo.Student;
CREATE TABLE dbo.Student (ID int, StudentName varchar(100));
CREATE TABLE dbo.Class (ID int, ClassName varchar(100));
CREATE TABLE dbo.StudentClass (ID int, ClassID int, StudentID int);
GO
CREATE OR ALTER PROCEDURE dbo.ResetPuzzle
AS
BEGIN
    SET NOCOUNT ON;
    TRUNCATE TABLE dbo.StudentClass;
    TRUNCATE TABLE dbo.Class;
    TRUNCATE TABLE dbo.Student;
    INSERT INTO dbo.Student (ID, StudentName) VALUES (1, 'Mark'), (2, 'John'), (3, 'Thomas');
    INSERT INTO dbo.Class (ID, ClassName) VALUES (1, 'Maths'), (2, 'English'), (3, 'Science');
    INSERT INTO dbo.StudentClass (ID, ClassID, StudentID) VALUES (1, 1, 1), (2, 2, 2), (3, 1, 3), (4, 2, 1), (5, 2, 3), (6, 3, 1);
END;
GO
EXEC dbo.ResetPuzzle;

The Starting Point: 19 Reads

The starting script is the one from the puzzle. It deletes the students first and the enrollments second.

EXEC dbo.ResetPuzzle;

SET STATISTICS IO ON;

DELETE s
FROM dbo.Student AS s
INNER JOIN dbo.StudentClass AS sc ON s.ID = sc.StudentID
INNER JOIN dbo.Class AS c ON c.ID = sc.ClassID
WHERE c.ClassName = 'English'
  AND sc.StudentID IN (SELECT sc2.StudentID FROM dbo.StudentClass AS sc2 GROUP BY sc2.StudentID HAVING COUNT(*) = 1);

DELETE sc
FROM dbo.StudentClass AS sc
INNER JOIN dbo.Class AS c ON c.ID = sc.ClassID
WHERE c.ClassName = 'English';

SET STATISTICS IO OFF;

The Answer: 4 Reads With a String of IDs

The students to delete are the ones with exactly one class, and that class is the cancelled one. Both facts come from StudentClass. One pass over that table can group it by student and keep only the students who qualify. STRING_AGG joins their IDs into one string. The Student table is then scanned once, and each row asks whether its ID is in the string. The enrollments go last.

The procedure below does that. It runs in one transaction, because two deletes that depend on each other must succeed or fail together. SET XACT_ABORT ON rolls everything back if a statement fails. STRING_AGG needs SQL Server 2017.

CREATE OR ALTER PROCEDURE dbo.RemoveClassFast @ClassName varchar(100)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    DECLARE @ClassID int = (SELECT ID FROM dbo.Class WHERE ClassName = @ClassName);
    DECLARE @Ids varchar(max) = (SELECT STRING_AGG(CONVERT(varchar(20), StudentID), ',')
                                 FROM (SELECT StudentID FROM dbo.StudentClass GROUP BY StudentID HAVING COUNT(*) = 1 AND MIN(ClassID) = @ClassID) AS g);
    BEGIN TRANSACTION;
    DELETE FROM dbo.Student WHERE CHARINDEX(',' + CONVERT(varchar(20), ID) + ',', ',' + @Ids + ',') > 0;
    DELETE FROM dbo.StudentClass WHERE ClassID = @ClassID;
    COMMIT TRANSACTION;
END;
GO
EXEC dbo.ResetPuzzle;

SET STATISTICS IO ON;

EXEC dbo.RemoveClassFast @ClassName = 'English';

SET STATISTICS IO OFF;

The total is 4 logical reads, against 19. Class and Student are read once. StudentClass is read twice: once to build the list, once to delete the enrollments. Nothing in this answer reads fewer pages.

Read the number with care. It is a score on three tiny tables. Every Student row searches the whole string. The cost grows with the table, and the string grows with the number of students. The trick works because the IDs are numbers with a comma on each side. It would need more care with text keys. Treat the 4 as the best score, not as the design to copy.

The Version to Use: 5 Reads, Set Based

The set based version keeps the same idea. One pass over StudentClass counts the classes of each student and flags the cancelled class. The delete joins that result to Student. There is no string to search, so it scales. It costs one read more here.

CREATE OR ALTER PROCEDURE dbo.RemoveClass @ClassName varchar(100)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    DECLARE @ClassID int = (SELECT ID FROM dbo.Class WHERE ClassName = @ClassName);
    BEGIN TRANSACTION;
    WITH Counts AS (
        SELECT StudentID, COUNT(*) AS Classes, MAX(CASE WHEN ClassID = @ClassID THEN 1 ELSE 0 END) AS InTarget
        FROM dbo.StudentClass
        GROUP BY StudentID
    )
    DELETE s
    FROM dbo.Student AS s
    JOIN Counts AS c ON c.StudentID = s.ID
    WHERE c.Classes = 1 AND c.InTarget = 1;
    DELETE FROM dbo.StudentClass WHERE ClassID = @ClassID;
    COMMIT TRANSACTION;
END;
GO
EXEC dbo.ResetPuzzle;

SET STATISTICS IO ON;

EXEC dbo.RemoveClass @ClassName = 'English';

SET STATISTICS IO OFF;

The total is 5 logical reads. Class and Student are read once, and StudentClass twice. The order matters. The students go first, while the enrollments they depend on still exist. The enrollments go second.

SELECT ID, StudentName FROM dbo.Student ORDER BY ID;

SELECT ID, ClassID, StudentID FROM dbo.StudentClass ORDER BY ID;

The result is the one the rules ask for. John is gone, and Mark and Thomas keep their other classes. The 4 read version gives the same result.

Test Both With Other Data

A cheap answer is worth nothing if it is wrong on other data. The helper below resets the data and adds a fourth student, Priya, who has no classes. Then it runs one of the two versions. The script runs both versions for English, Maths and Geography, which does not exist.

CREATE OR ALTER PROCEDURE dbo.CheckRun @Version varchar(10), @ClassName varchar(100)
AS
BEGIN
    SET NOCOUNT ON;
    EXEC dbo.ResetPuzzle;
    INSERT INTO dbo.Student (ID, StudentName) VALUES (4, 'Priya');
    IF @Version = 'Fast' EXEC dbo.RemoveClassFast @ClassName = @ClassName; ELSE EXEC dbo.RemoveClass @ClassName = @ClassName;
    SELECT @Version AS Version, @ClassName AS ClassName, (SELECT COUNT(*) FROM dbo.Student) AS Students, (SELECT COUNT(*) FROM dbo.StudentClass) AS Enrollments;
END;
GO
EXEC dbo.CheckRun @Version = 'Fast', @ClassName = 'English';
EXEC dbo.CheckRun @Version = 'Set', @ClassName = 'English';
EXEC dbo.CheckRun @Version = 'Fast', @ClassName = 'Maths';
EXEC dbo.CheckRun @Version = 'Set', @ClassName = 'Maths';
EXEC dbo.CheckRun @Version = 'Fast', @ClassName = 'Geography';
EXEC dbo.CheckRun @Version = 'Set', @ClassName = 'Geography';

English removes John only, so Mark, Thomas and Priya remain. Maths removes two enrollments and no student. Geography does not exist, so nothing changes. Priya survives every run, because Priya never took any of the classes.

Correct Is Not the Same as Cheap

Other correct versions cost more. This one saves the affected student IDs in a table variable with the OUTPUT clause. The table variable adds reads of its own.

EXEC dbo.ResetPuzzle;

SET STATISTICS IO ON;

DECLARE @ClassID int = (SELECT ID FROM dbo.Class WHERE ClassName = 'English');
DECLARE @Affected TABLE (StudentID int);
DELETE FROM dbo.StudentClass OUTPUT deleted.StudentID INTO @Affected WHERE ClassID = @ClassID;
DELETE s FROM dbo.Student AS s
WHERE s.ID IN (SELECT StudentID FROM @Affected)
  AND NOT EXISTS (SELECT 1 FROM dbo.StudentClass AS sc WHERE sc.StudentID = s.ID);

SET STATISTICS IO OFF;

The result is right, and the total is 12 reads. The table variable costs 3 reads to fill and 3 to read. When you chase the fewest logical reads, check where each read comes from.

What Does Not Work

The shortest delete removes only the enrollments of the cancelled class. It is wrong, because it leaves John in the student table with no classes. It is not even cheap. With the join to Class it reads 7 pages, the same as the second delete of the starting script.

EXEC dbo.ResetPuzzle;

SET STATISTICS IO ON;

DELETE sc FROM dbo.StudentClass AS sc INNER JOIN dbo.Class AS c ON c.ID = sc.ClassID WHERE c.ClassName = 'English';

SET STATISTICS IO OFF;

SELECT s.StudentName, COUNT(sc.ID) AS Classes
FROM dbo.Student AS s
LEFT JOIN dbo.StudentClass AS sc ON sc.StudentID = s.ID
GROUP BY s.StudentName
ORDER BY s.StudentName;

A foreign key with ON DELETE CASCADE does not help. Cascade deletes the child rows when a parent row goes. Rule one needs the opposite: delete the parent when the last child goes. SQL Server has no such option, so the delete has to say it.

Cleaning up afterward is the other tempting plan. Delete the enrollments, then delete every student with no enrollment. That passes on this data, but it deletes students who never took any class. The post Delete the Wrong Row: A Cheap Answer That Passes the Test shows the exact case.

What to Remember

Count the touches of each table. A self join or a subquery that runs per row multiplies the reads. Compute what you need about each group in one pass. A count and a flag are enough here. Then delete from the result. Order the deletes so the evidence still exists when you need it.

Test the delete with data the puzzle never showed you. Two cases break the tempting answers: a student with no classes and a class that does not exist. The fewest logical reads mean nothing if the result is wrong. When you finish, run the cleanup script.

USE master;
GO
IF DB_ID(N'StudentClassSolutionDemo') IS NOT NULL
BEGIN
    ALTER DATABASE StudentClassSolutionDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE StudentClassSolutionDemo;
END;

A low read count is not a design, it is a score on one data set.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.