SQL SERVER – DELETE Qualified Rows From Multiple Tables – Part 2

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.

SQL SERVER - DELETE Qualified Rows From Multiple Tables - Part 2 del-puz0-800x305

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)

, ,
Previous Post
SQL SERVER – Best Value for Max Worker Count
Next Post
SQL SERVER – Show Primary Key for a Single Table – sp_pkeys

Related Posts

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 ?

    Reply
    • 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.

      Reply
  • 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

    Reply

Leave a Reply

Menu