SQLTeam.com | Weblogs | Forums

Rewriting the query for performance


#1

I am just learning the sql. I have this query and trying to optimize the query rewriting.Please help me out more efficient way of writing this query--Thanks!!

DECLARE @Test TABLE ( ID INT ,DetailID INT , PrevID INT );

INSERT INTO @Test ( ID , DetailID ,SaleID)

SELECT a.ID , a.DetailID ,b.PrevID FROM tbla A
LEFT JOIN TBLb B ON A.id = B.id;
DELETE FROM tbla WHERE id IN ( SELECT ID FROM @Test );

UPDATE tblc SET Flag = 'y'
WHERE id IN ( SELECT ID FROM @Test UNION ALL SELECT PrevID FROM @Test );


#2

Seems like you are deleting all rows from tbla.
A faster way for this: truncate table tbla

The where sentence could be replaced with:
where exists (select 1 from @test where tblc in (@test.id,@test.previd))