Post reply

Rewritng a delete statement

  • 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

      

     

  • April 3, 2006 at 2:48 pm

    #630527

    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.

  • April 3, 2006 at 3:48 pm

    #630534

    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

     

  • April 3, 2006 at 4:42 pm

    #630538

    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.

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

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