RE: SQL delete based on aggregate function?

I would like someone from the SQL lap working on the optimiser address this
thread.  Are you out there?

Don
Tully Consulting LLC

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Walden H. Leverich
Sent: Thursday, January 20, 2005 1:17 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL delete based on aggregate function?


>The SQL optimizer recognizes that the subquery never changes...

That _should_ be the case. The inner select is a non-correlated subquery
and as such should NOT be rerun every time. However, the performance I
get when I do run such a query seems to indicate otherwise, at least on
a V5R2 box.

As a test I tried the following statement on a 270 w/5.2 doing nothing
else:

select sum(latitude) from zip where state in (select state from zip
group by state having count(*) > 1000)

Which should return a sum of the latitudes (a useless value, but forces
sql to read each record in the resultset). Zip has just over 800,000
rows (Canadian zips too)

As far as I can tell that is a NON-Correlated subquery. However, I tried
running the query through visual explain and while I killed the query
before it ended, I did see this in the joblog:

Message ID . . . . . . :   CPI4325
Message . . . . :   Temporary result file built for query.
Cause . . . . . :   A temporary result file was created to contain the
results
  of the query for reason code 11.  This process took 0 minutes and 1.3
...
  11 - The query optimizer creates a run-time temporary file in order t
implement certain correlated group by queries.

FWIW, I ran the same query on SQLServer and got the answer in 3 seconds.


-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.)


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.