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.
You can read this blog post to understand what was the actual puzzle and conditions. Puzzle – DELETE Qualified Rows From Multiple Tables – Win USD 1000 Worth Class. 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.
What we want to do
To active the final outcome from the original setup, we needed to write a query which takes the least amount of the reads. I had given a sample query which was taking 19 logical reads and that was way higher. Your task was to come up with the solution which takes lease amount of the logical reads to accomplish my tasks.
I was very much delighted that I have got many good answers to this DIFFICULT puzzle.
Let us see first the solution of Harsh who has won the FREE Access to the SQL Server Performance Tuning Practical Workshop – Recorded Classes.
Solution by Harsh – Winner
Only 4 logical reads.
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
Harsh, send me email to get your FREE access as you are winner. Here is the link to his solution.
Here are other participants who also provided a valid answer and also eligible for 50% off for my recorded course.
- Calunoth Allerton
- Andrew Wait
- Sanjay Monpara
- Bryan Rebok
- Nitin Dhiman
If you can think of the better solution, you can always post them here.
Reference: Pinal Dave (https://blog.sqlauthority.com)
i am new to this blog, i have one question.
Q: Why we need to have this much big query ? To achieve this, why can’t we delete data where className = ‘English’ !
when i have perform delete operation on table logical reads 1 and physical reads are 0.
Please refer below Message,
Table ‘EMp’. 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 row(s) affected)
Correct me if i am missing anything !
select * from (giventablename)
delete from cte