Query rewrite for performance

kccrga 116 Reputation points

The query below runs for more than three hours to complete. This both the tables have more than 100 millions rows. Please suggest how we can rewrite or tune it.

;With CTE_TO
Select top 10000000 max(ID) as ID, FormID
FROM [DB_history].[dbo.[Form] with (nolock)
Where Code =1 group by FormID
SELECT a.FormID ,b.ODSID as Maxdeleterecord, max(a.ID) as ID
From [DB_History].[dbo].[Form] as a
inner join CTE as b on a.FormID=b.FormID
Group by b.ODSID,a.entrantFormID HAVING max(a.ID) = b.ID

delete b
from [DB.[dbo].[Form] b
where exists (Select 1 from DelMax a where a.FormID=b.FormID)

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,596 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Olaf Helper 42,921 Reputation points

    Check the execution plan to see if e.g. a suitable index is used or not = missing index.
    You can share the execution plan by https://www.brentozar.com/pastetheplan/

    0 comments No comments