Will this two SQL statements get the same result?

16 Dec 2005 4:21 PM

Matthew

delete from OrdersAudit
where OrderNbr not in (
select OrderNbr from Orders)

delete OrdersAudit
from OrdersAudit,Orders
where OrdersAudit.OrderNbr <> Orders.OrderNbr

How much do you think the performance difference will be? There's clustered
index on OrderNber for both tables.

Thanks

16 Dec 2005 4:35 PM

Aaron Bertrand [SQL Server MVP]

> delete from OrdersAudit
> where OrderNbr not in (
> select OrderNbr from Orders)
>
> delete OrdersAudit
> from OrdersAudit,Orders
> where OrdersAudit.OrderNbr <> Orders.OrderNbr
>
> How much do you think the performance difference will be?

These are nowhere near the same queries. But you shouldn't need us to tell
you that... a simple test will demonstrate that there i a huge difference.

use tempdb
go

create table dbo.Orders(OrderNbr INT)
create table dbo.OrdersAudit(OrderNbr INT)
go

set nocount on
insert orders select 1
insert orders select 2
insert orders select 3
insert orders select 4
insert ordersaudit select 1
insert ordersaudit select 3
insert ordersaudit select 4
insert ordersaudit select 6
insert ordersaudit select 9
insert ordersaudit select 11
go

delete from OrdersAudit
where OrderNbr not in (
select OrderNbr from Orders)
go

select * from orders
select * from ordersaudit
go

delete orders
delete ordersaudit
go

insert orders select 1
insert orders select 2
insert orders select 3
insert orders select 4
insert ordersaudit select 1
insert ordersaudit select 3
insert ordersaudit select 4
insert ordersaudit select 6
insert ordersaudit select 9
insert ordersaudit select 11
go

delete OrdersAudit
from OrdersAudit,Orders
where OrdersAudit.OrderNbr <> Orders.OrderNbr

select * from orders
select * from ordersaudit
go

drop table dbo.Orders, dbo.OrdersAudit;
go

16 Dec 2005 5:09 PM

Matthew

Thanks, Aaron,
I already found the problem in second query, that why I put this post here :)
Kindly please tell me how can I improve the performance of the first one? it
takes 2 days to delete 2 million records from a 8 million records table?

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> > delete from OrdersAudit
> > where OrderNbr not in (
> >       select OrderNbr from Orders)
> >
> > delete OrdersAudit
> > from OrdersAudit,Orders
> > where OrdersAudit.OrderNbr <> Orders.OrderNbr
> >
> > How much do you think the performance difference will be?
>
>
> These are nowhere near the same queries. But you shouldn't need us to tell
> you that... a simple test will demonstrate that there i a huge difference.
>
> use tempdb
> go
>
> create table dbo.Orders(OrderNbr INT)
> create table dbo.OrdersAudit(OrderNbr INT)
> go
>
> set nocount on
> insert orders select 1
> insert orders select 2
> insert orders select 3
> insert orders select 4
> insert ordersaudit select 1
> insert ordersaudit select 3
> insert ordersaudit select 4
> insert ordersaudit select 6
> insert ordersaudit select 9
> insert ordersaudit select 11
> go
>
> delete from OrdersAudit
> where OrderNbr not in (
>        select OrderNbr from Orders)
> go
>
> select * from orders
> select * from ordersaudit
> go
>
> delete orders
> delete ordersaudit
> go
>
> insert orders select 1
> insert orders select 2
> insert orders select 3
> insert orders select 4
> insert ordersaudit select 1
> insert ordersaudit select 3
> insert ordersaudit select 4
> insert ordersaudit select 6
> insert ordersaudit select 9
> insert ordersaudit select 11
> go
>
> delete OrdersAudit
>   from OrdersAudit,Orders
> where OrdersAudit.OrderNbr <> Orders.OrderNbr
>
> select * from orders
> select * from ordersaudit
> go
>
> drop table dbo.Orders, dbo.OrdersAudit;
> go
>
>
>

16 Dec 2005 4:37 PM

Alexander Kuznetsov

most likely the second one will delete everything

select 1 OrderNbr into #OrdersAudit
union all select 2
select 1 OrderNbr into #Orders
union all select 2

select * from #OrdersAudit

delete #OrdersAudit
from #OrdersAudit,#Orders
where #OrdersAudit.OrderNbr <> #Orders.OrderNbr

select * from #OrdersAudit
--- 0 rows selected