question

premkumardr avatar image
0 Votes"
premkumardr asked MelissaMa-msft edited

Query rewrite for performance

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)

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @premkumardr,

We recommend that you post CREATE TABLE statements for your tables ([DB_history].[dbo.[Form] and other related tables) together with INSERT statements with sample data.

Please provide the code of CTE in your query or your complete query.

Please also remember to provide your execution plan if possible.

Best regards,
Melissa

0 Votes 0 ·

1 Answer

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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/

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.