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

 

[bborissov]

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

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[fredong]

HI bborissov,
Thanks for your help. Just to make sure your statement does delete the same amount of rows as mine?


Thanks.

 

[bborissov]

Made a good backup first. That query will delete all records from @Aa where OrderId is not a MAX(OrderId) for BaseId.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[fredong]

Hi bborissov,
how about this one with the 'IN' statemnt for a cursor. Is there a way to tune it? Thanks.

SELECT E.AKEY AS AKEY, E.PKEY
FROM BCC.dbo.COL FF (nolock) INNER JOIN
dbo.EXPT E (nolock) ON FF.ID = E.ID
where E.ID in (Select ID from @tEx)
and FF.PC = E.PC
and FF.PC = '4'
and FF.TC = 'DY'
and FF.MT = 'DEL'


P.S BCC is from another database

 

[bborissov]

All IN clauses could be replaced with INNER JOIN
SELECT E.AKEY AS AKEY, E.PKEY
FROM BCC.dbo.COL FF (nolock)
INNER JOIN
(SELECT E.PKEY
FROM dbo.EXPT (nolock)
INNER JOIN @tEx tEx ON dbo.EXPT.Id = tEx.id) E
ON FF.ID = E.ID
where FF.PC = E.PC
and FF.PC = '4'
and FF.TC = 'DY'
and FF.MT = 'DEL'
[/code]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

Sorry for ugly code

SELECT E.AKEY AS AKEY, E.PKEY
       FROM  BCC.dbo.COL FF (nolock)
INNER JOIN
(SELECT E.PKEY
        FROM dbo.EXPT (nolock)
        INNER JOIN @tEx tEx ON dbo.EXPT.Id = tEx.id) E
ON FF.ID = E.ID
where  FF.PC = E.PC
       and FF.PC = '4'
       and FF.TC = 'DY'
       and FF.MT = 'DEL'

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[fredong]

Thank you very much.

 

[fredong]

HI

this statement does not work because it not recognize the Aa alias. Please advise. Thanks.

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

 

[bborissov]

Sorry, my mstake:

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

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[fredong]

it still tells me incorrect syntax on

Line 392: Incorrect syntax near 'Aa'.
Line 395: Incorrect syntax near 'Tbl1'.

 

[bborissov]

This works for me:

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

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[fredong]

it stated
Server: Msg 207, Level 16, State 3

Invalid column name 'BaseID'.

Whiich I do have the column exist.

 

[fredong]

Thanks I got it.

 

[SQLSister]

BTW if you are performance tuning the first thing is to get rid of the cursor. Cursors are the worst performance hogs out there.



Questions about posting. See faq183-874

 

[fredong]

Hi bborissov.

Is there a way to replace the 'IN' within an 'IN' with Inner join? Thanks.

delete from @Snp
where EUID in
(select EUID
from @P4E
where CID in
(
Select CID from @4List
)
)
and TC in (i','t')

 

[Catadmin]

Well, I'm not bborissov, but the answer to your question is yes. Just do the same thing you have been doing. Take the table on the inner most IN statement (@4List) and join it to @P4E on the CID column. Then you can join @P4E to @Snp on the EUID column. So you'll have 3 Inner Joins without IN statements.

Just remember, in a Delete statement, you have to repeat your FROM statement to get it to work.

Delete from @Snp
From @Snp
Join @P4E...

I'm not going to finish the code for you because it'll be good practice. When you've got it written, post it and we'll let you know if you're on the right track.



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]

this is what I got let me know if is right.


delete from @Snp
from @Snp Snap INNER JOIN
(select E.EUID AS EUID
from @P4E E, @4List S
where E.CID =S.CID) TblA
ON TblA.EUID = Snap.EUID
Where snap.TC in ('i','t')

 

[Catadmin]

You don't even need that subquery as far as I can tell. Remove it and make the subquery a second INNER JOIN clause.



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]

how can I do that. Please advise. Thanks.