Rewritng a delete statement

Question

Rewritng a delete statement

Ross Sines

Ten Centuries

Points: 1288
More actions
April 3, 2006 at 12:26 pm

#152880

The statement below was not written by me and it takes forever ( after 30 minutes, I killed it) to delete the specified rows from a table of nearly 3 million records, that is indexed BTW.
delete from s_report_special_flags where case_id in (select vwRptSubInactiveEvent.case_id
from vwRptSubInactiveEvent, s_case_id_list
where (vwRptSubInactiveEvent.Inactive_Closed_Date is null or
       vwRptSubInactiveEvent.Inactive_Closed_Date < s_case_id_list.End_Date)
and s_case_id_list.case_id = vwRptSubInactiveEvent.case_id
and s_case_id_list.case_person_id = vwRptSubInactiveEvent.case_person_id
and s_report_special_flags.case_id = vwRptSubInactiveEvent.case_id
and s_report_special_flags.case_person_id = vwRptSubInactiveEvent.case_person_id
and s_case_id_list.report_id = s_report_special_flags.report_id
and s_case_id_list.report_id = @Report_ID)
and report_id = @Report_ID

I want to rewrite the statement to look like this:
delete from s_report_special_flags
from vwRptSubInactiveEvent, s_case_id_list
where (vwRptSubInactiveEvent.Inactive_Closed_Date is null or
       vwRptSubInactiveEvent.Inactive_Closed_Date < s_case_id_list.End_Date)
and s_case_id_list.case_id = vwRptSubInactiveEvent.case_id
and s_case_id_list.case_person_id = vwRptSubInactiveEvent.case_person_id
and s_report_special_flags.case_id = vwRptSubInactiveEvent.case_id
and s_report_special_flags.case_person_id = vwRptSubInactiveEvent.case_person_id
and s_case_id_list.report_id = s_report_special_flags.report_id
and s_case_id_list.report_id = @Report_ID)
and s_report_special_flags.report_id = @Report_ID
When I ran this code in our test database it took a few minutes to delete the records I needed removed, but it worked. I thought I would just run it by a few eyeballs to get an opinion as to why I may not be able to change the T-SQL to my version.
Thanks,
Ross

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

I had to re-write this so I could understand it a bit better. I believe your change is superior and accomplishes the same thing. Basically, you use a direct join where the original uses an implicitly generated table to join and I can only guess that is where you are bogging down... I have no idea of the indexes on these tables....

DELETE s_report_special_flags

WHERE case_id IN( SELECT vwRptSubInactiveEvent.case_id

FROM vwRptSubInactiveEvent

INNER JOIN s_case_id_list ON( s_case_id_list.case_id = vwRptSubInactiveEvent.case_id

AND s_case_id_list.case_person_id = vwRptSubInactiveEvent.case_person_id

AND s_report_special_flags.case_id = vwRptSubInactiveEvent.case_id

AND s_report_special_flags.case_person_id = vwRptSubInactiveEvent.case_person_id

AND s_case_id_list.report_id = s_report_special_flags.report_id

AND s_case_id_list.report_id = @Report_ID)

WHERE (vwRptSubInactiveEvent.Inactive_Closed_Date IS NULL

OR vwRptSubInactiveEvent.Inactive_Closed_Date < s_case_id_list.End_Date)

AND report_id = @Report_ID

-----------------------------------------------------------------------------------------DELETE s_report_special_flags

FROM vwRptSubInactiveEvent

INNER JOIN s_case_id_list ON( s_case_id_list.case_id = vwRptSubInactiveEvent.case_id

AND s_case_id_list.case_person_id = vwRptSubInactiveEvent.case_person_id

AND s_report_special_flags.case_id = vwRptSubInactiveEvent.case_id

AND s_report_special_flags.case_person_id = vwRptSubInactiveEvent.case_person_id

AND s_case_id_list.report_id = s_report_special_flags.report_id

AND s_case_id_list.report_id = @Report_ID)

WHERE( vwRptSubInactiveEvent.Inactive_Closed_Date IS NULL

OR vwRptSubInactiveEvent.Inactive_Closed_Date < s_case_id_list.End_Date)

AND s_report_special_flags.report_id = @Report_ID

I wasn't born stupid - I had to study.

Ross Sines Ten Centuries Points: 1288 More actions · Answer 2

Farrell:

I did have to tweek your suggestion for better syntax and clarification just a bit and when I reran it in my test environment it worked even much faster than before. Here is my revison.

DELETE s_report_special_flags

FROM dbo.vwRptSubInactiveEvent INNER JOIN

dbo.s_case_id_list ON dbo.vwRptSubInactiveEvent.case_id = dbo.s_case_id_list.case_id AND

dbo.vwRptSubInactiveEvent.case_person_id = dbo.s_case_id_list.case_person_id INNER JOIN

dbo.s_report_special_flags ON dbo.s_case_id_list.case_id = dbo.s_report_special_flags.case_id AND

(dbo.s_case_id_list.report_id = @Report_id)

WHERE ( vwRptSubInactiveEvent.Inactive_Closed_Date IS NULL

OR vwRptSubInactiveEvent.Inactive_Closed_Date < s_case_id_list.End_Date)

AND (dbo.s_report_special_flags.Report_id = @Report_Id)

Much Thanks for your input,

Ross

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

Glad to help. I did not know your tables, so I just had to wing what I saw.

I wasn't born stupid - I had to study.