Query rewrite for performance

kccrga 116 Reputation points
2021-10-01T04:05:09.517+00:00

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
as
(
Select top 10000000 max(ID) as ID, FormID
FROM [DB_history].[dbo.[Form] with (nolock)
Where Code =1 group by FormID
)
,DelMax
as
(
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)

Transact-SQL
Transact-SQL
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
    2021-10-01T05:38:01.997+00:00

    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