optimize delete statement

Hello,

How can I optimize this delete statement. The table estimation_du_moins contains 200000000 rows :
USE TBA_VCF_V2_VCGP


declare @doIdAsupp int
set @doIdAsupp = 3 -- 1,2,3,4,5,6,9

-- TBA
declare @temp_tba table (id int)

insert into @temp_tba
SELECT TBA_AFFAIRE.AFF_ID
FROM  TBA_AFFAIRE
INNER JOIN dbo.SYN_PMG_CP ON TBA_AFFAIRE.CP_ID = dbo.SYN_PMG_CP.CP_ID
INNER JOIN dbo.SYN_PMG_DA ON dbo.SYN_PMG_CP.DA_ID = dbo.SYN_PMG_DA.DA_ID
INNER JOIN dbo.SYN_PMG_DR ON dbo.SYN_PMG_DA.DR_ID = dbo.SYN_PMG_DR.DR_ID
INNER JOIN dbo.SYN_PMG_DD ON dbo.SYN_PMG_DR.DD_ID = dbo.SYN_PMG_DD.DD_ID
INNER JOIN dbo.SYN_PMG_DO ON dbo.SYN_PMG_DD.DO_ID = dbo.SYN_PMG_DO.DO_ID
WHERE dbo.SYN_PMG_DO.DO_ID = @doIdAsupp

--SELECT AFF_ID FROM dbo.TBA_AFFAIRE WHERE AFF_ID IN (select id from @temp_tba)


-- Clean TBA_ESTIMATION_DU_MOIS
BEGIN TRAN
DELETE dbo.TBA_ESTIMATION_DU_MOIS
WHERE AFF_ID IN (select id from @temp_tba)
COMMIT TRAN

-- Clean TBA_PREVISION_PERIODE
BEGIN TRAN
DELETE dbo.TBA_PREVISION_PERIODE
WHERE AFF_ID IN (select id from @temp_tba)
COMMIT TRAN

-- Clean TBA_STADE_AFFAIRE_PERIODE
BEGIN TRAN
DELETE dbo.TBA_STADE_AFFAIRE_PERIODE
WHERE AFF_ID IN (select id from @temp_tba)
COMMIT TRAN

-- Clean TBA_FIN_AFFAIRE_CPLT
BEGIN TRAN
DELETE dbo.TBA_FIN_AFFAIRE_CPLT
WHERE AFF_ID IN (select id from @temp_tba)
COMMIT TRAN

-- Clean TBA_FIN_AFFAIRE
BEGIN TRAN
DELETE dbo.TBA_FIN_AFFAIRE
WHERE AFF_ID IN (select id from @temp_tba)
COMMIT TRAN

---- Clean TBA_INDIC_FI
BEGIN TRAN
DELETE dbo.TBA_INDIC_FI
WHERE CI_ID IN (select id from @temp_tba)
COMMIT TRAN

-- Clean TBA_AFFAIRE
BEGIN TRAN
DELETE dbo.TBA_AFFAIRE
WHERE AFF_ID IN (select id from @temp_tba)
COMMIT TRAN

-- SII
declare @temp_sii table (id int)

insert into @temp_sii
SELECT SII_MONTANT.SII_ID
FROM SII_MONTANT
INNER JOIN dbo.SYN_PMG_CP ON SII_MONTANT.CP_ID = dbo.SYN_PMG_CP.CP_ID
INNER JOIN dbo.SYN_PMG_DA ON dbo.SYN_PMG_CP.DA_ID = dbo.SYN_PMG_DA.DA_ID
INNER JOIN dbo.SYN_PMG_DR ON dbo.SYN_PMG_DA.DR_ID = dbo.SYN_PMG_DR.DR_ID
INNER JOIN dbo.SYN_PMG_DD ON dbo.SYN_PMG_DR.DD_ID = dbo.SYN_PMG_DD.DD_ID
INNER JOIN dbo.SYN_PMG_DO ON dbo.SYN_PMG_DD.DO_ID = dbo.SYN_PMG_DO.DO_ID
WHERE dbo.SYN_PMG_DO.DO_ID = @doIdAsupp

--SELECT SII_ID FROM dbo.SII_MONTANT WHERE SII_ID IN (select id from @temp_sii)

-- Clean SII_MONTANT
BEGIN TRAN
DELETE dbo.SII_MONTANT
WHERE SII_ID IN (select id from @temp_sii)
COMMIT TRAN

Thanks

Bibi
bibi92Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Something like this:
DECLARE @temp_tba TABLE (id int PRIMARY KEY)

...


DECLARE @RowCount integer

SET ROWCOUNT 100000 	-- change as appropriate
SET @RowCount = 1
WHILE @RowCount > 0 
    BEGIN
        DELETE  e
        FROM    dbo.TBA_ESTIMATION_DU_MOIS e
                INNER JOIN @temp_tba t ON e.AFF_ID = t.id

        SET @RowCount = @@ROWCOUNT
    END

Open in new window

0
bibi92Author Commented:
Hello,

Thanks, but it's the same thing.

Regards

Bibi
0
Anthony PerkinsCommented:
Fair enough.  Sorry I could not help any more.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bibi92Author Commented:
Thanks a lot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.