Earlier this week, I had announced a contest to get Recorded PASS of my extremely popular training SQL Server Performance Tuning Practical Workshop – Recorded Classes. As the price of this class high, I wanted to keep my contest difficult as well. Today we will see the winners and solutions. I have also announced the winners and solution of this puzzle in the follow-up blog post here: Winners and Solution – DELETE Qualified Rows From Multiple Tables.
Update: My test was wrong and I have updated a few details in the following blog post.
Though the contest is over we have received another amazing solution from the Arun Singh. His solution is very efficient and does only 7-page reads. I strongly recommend everyone to see his solution and learn how efficiently he is able to do perform the task.
DELETE Qualified Rows
Here I am reproducing the puzzle in the simple words –
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.
Solution by Arun
SET STATISTICS IO ON; -- Get ClassID DECLARE @classID INT SELECT @classID=id FROM class WITH(NOLOCK) WHERE className='English'; -- Delete from StudentClass DELETE FROM studentclass WHERE classID= @ClassID; -- Delete from Student DELETE S FROM student S LEFT JOIN StudentClass SC WITH(NOLOCK) ON SC.StudentID=S.id WHERE SC.StudentID IS NULL
When I ran above query with the statistics IO, I get the following results.
Table ‘Class’. Scan count 1, logical reads 1
Table ‘StudentClass’. Scan count 1, logical reads 1
(3 rows affected)
Table ‘Student’. Scan count 1, logical reads 2
Table ‘StudentClass’. Scan count 1, logical reads 3
(1 row affected)
The total logical reads for the above query was 7 pages on my machine. Fantastic solution Arun, you did well.
Though the contest of deleting qualified rows is over, if you want, I can give you 50% discount in the SQL Server Performance Tuning Practical Workshop – Recorded Class.
Reference: Pinal Dave (https://blog.sqlauthority.com)