Delete with NOT IN cause performance issue

[fredong]

Hi,
I am trying to replace the Delete statement because it causedperformance issue and take up CPU. I try to replace the using 'NOT IN' I am not sure anybody has any advice for me. Thanks.


delete from @AA
where orderid not in
(
select max(orderid) as orderID
from @AA
group by BaseID

 

[Catadmin]

Look up JOIN in Books Online. It should give you details. You have all the information there on what you're joining everything too.

Hint, the columns you are joining on are in all the WHERE clauses of the 3rd to last post you made.




Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[fredong]

I need example. Can somebody helps? Thanks.

 

[fredong]

Hi bborissov,
I tried your solution and the delete solution that you provided deleted more rows than my original statment. Do you know why?

--your solution

DECLARE @Aa TABLE (BaseID int, OrderId int)
INSERT INTO @Aa VALUES (1,1)
INSERT INTO @Aa VALUES (1,2)
SELECT * from @AA

Delete from @AA
FROM @Aa Aa
LEFT JOIN
(SELECT BaseID, MAX(OrderId) AS OrderId
FROM @Aa
GROUP BY BaseId) Tbl1
ON Aa.BaseId = Tbl1.BaseId AND
Aa.OrderId = Tbl1.OrderId
where Tbl1.BaseId IS NULL
SELECT * from @AA


--my original statement


delete from @AA
where orderid not in
(
select max(orderid) as orderID
from @AA
group by BaseID

 

[ESquared]

For a bit the FROM FROM was freaking me out, then I remembered that I don't use the first FROM ever, so never mind!

DELETE FROM X WHERE <-- yuck
DELETE X WHERE <-- I like this

--makes sense
DELETE X
FROM
   MyTable X
   INNER JOIN Y ON Blah

--weird to me
DELETE FROM X FROM MyTable X
   INNER JOIN Y ON Blah

 

[Catadmin]

Esquared,

Either your "makes sense" code or my FROM FROM code will work. I just like being thorough so if another DBA has to look at my code he/she doesn't get confused by what I'm doing because I didn't show my work.

Good grief, the math classes I took in High School have completely messed with my head. "Show your work! Show your work!" @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"