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)
5 Comments. Leave new
Table ‘Student’. Scan count 0, logical reads 0
(0 rows affected)
so , John is still present ! From my understanding, John should be deleted , isn’t it ?
That is true. I re-ran everything. I was wrong. You are correct.
John should have been deleted too!
Anyway, I guess I did not do a complete test. I still think the answer was quite good and a little modification to it can fix the solution.
I have updated original blog post and now it reflects the correct information. Thank you, Sabin!
I thank you for the great work you are doing for t-sql community
SET STATISTICS IO ON
Declare @ClassID As Int=0,@StudentID VarChar(500)=”;
Select @ClassID=C.ID,@StudentID+=Convert(Varchar(50),StudentID)+’,’
From StudentClass S Inner JOin Class C On C.ID=S.ClassID Where ClassName=’English’
Set @StudentID=Left(@StudentID,Len(@StudentID)-1)
Delete From StudentClass Where ClassID=@ClassID
;
WiTH CTE
As
(Select Convert(XML,”+Replace(@StudentID,’,’,”)+”) As XMLData
),
R
As
(Select result As StudentID
From CTE
Cross Apply(Select r.value(‘.’,’NvarChar(Max)’) As Result
From XMLData.nodes(‘t’)as records(r)
)R
)
Delete S From StudentClass C Inner JOin R On R.StudentID=C.StudentID
Full Outer Join Student S On C.StudentID=S.ID
Where C.ID Is Null