RE: SQL delete based on aggregate function?

I created the equivalent table in SQL server to do some more testing:

STOCKNO char 25
WORD char 80
FULLDESC char 80
LOCATION char 20

select count(*) from wordfile2;
94111

select count(*) from (
select word from wordfile2
group by word having count(*) = 1) as word;
16504

select * from wordfile2 where word in
(select word from wordfile2
group by word having count(*) = 1);

Results returned in 8 seconds,
16,504 rows displayed.

delete from wordfile2 where word in
(select word from wordfile2
group by word having count(*) = 1);

Results returned in 4 seconds.
(16504 row(s) affected)

This is on a stock table with no indexes or keys.

There's definitely something going on (or not going on) with the OS/400
optimizer that SQL server trumped it so soundly.

The 94111 rows is the same as on OS/400. I removed all words that were 2
characters or less. Running the same SQL statements in OS/400 still result
in long run times when using the "in" syntax. 

It appears that SQL server performs the static optimization Walden talked
about, where the subselect is created once and used for the entire
statement. Here is a syntax diagram from SQL server (after refreshing the
table data and running a select):

select * from wordfile2 where word in
(select word from wordfile2
group by word having count(*) = 1);

Table Scan, cost: 33%
->
Hash Match/Aggregate, cost: 25%
->
Compute Scalar, cost: 0%
->
Filter, cost: 0%
And
Table Scan, cost: 33%
->
Hash Match/Right Semi Join, cost: 10%
->
Select, cost: 0%



Loyd Goodbar
Senior programmer/analyst
BorgWarner
E/TS Water Valley
662-473-5713

-----Original Message-----
From: Walden H. Leverich [mailto:WaldenL@xxxxxxxxxxxxxxx] 
Sent: Thursday, January 20, 2005 13:46
To: Midrange Systems Technical Discussion
Subject: RE: SQL delete based on aggregate function?

As an update, I let the statement complete running in visual explain.
While I didn't get an answer, probably because the connection timed out,
I did get visual explain results. Total execution time: 1,695,503,880
microseconds.  That's 1,695 seconds, or 28+ MINUTES!

-Walden


------------
Walden H Leverich III
President & CEO
Tech Software
(516) 627-3800 x11
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)