Date:         Fri, 28 Sep 2001 01:26:14 -0700
Reply-To:     Niels Stout <niels.stout@CMG.NL>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Niels Stout <niels.stout@CMG.NL>
Organization: http://groups.google.com/
Subject:      Re: Performance problems in V8.2
Content-Type: text/plain; charset=ISO-8859-1

Hi Stephen,

have you tried this (from SAS online-docs):

With subqueries, use IN instead of EXISTS when possible. For example,
the following queries produce the same result, but the second query is
more efficient:
proc sql;
   select *
      from proclib.payroll p
      where exists (select *
                    from staff s
                    where p.idnum=s.idnum
                       and state='CT');

proc sql;
   select *
      from proclib.payroll
      where idnum in (select idnum
                         from staff
                         where state='CT');

Cheers,

Niels

stephen.dunn@HIGHWAY-INSURANCE.CO.UK (Stephen Dunn) wrote in message news:<B99009D5BA6EBA459A6C1415455673821AFE92@bwsex01.highway-insurance.co.uk>...
> We have a number of SQL delete steps in our data warehouse update process
> - running on a Windows NT 4 server. They delete observations based on the
> correlated subquery below. The table in the subquery (obs=42) has a
> composite index (claimno,yoa) and the master file (obs=680000) has no
> indexes. In version 8.1 this step took 30 secs but in version 8.2 it takes
> over 6 minutes.  Other deletes are also showing order-of-magnitude
> worsening.
>
>
> proc sql;
> 1226            delete
> 1227               from dsetout.clmtran as p
> 1228               where exists (select *
> 1229                             from  clmtran as c
> 1230                             where p.yoa=c.yoa and p.claimno=c.claimno
> 1231                             );
> NOTE: 42 rows were deleted from DSETOUT.CLMTRAN.
>
> Have other people had similar experiences?
>  Any suggestion for a workaround?
>  You'd think SAS would test this kind of thing wouldn't you?
>
> regards
> Stephen Dunn
> Highway Insurance
> 171 King's Road
> Brentwood
> Essex
> CM14 4EJ
> Tel  (01277 266) 253
> Fax 01277 262828
> Stephen.Dunn@highway-insurance.co.uk <mailto:Stephen_d@hway.co.uk>
>
>
>
> Member of the General Insurance Standards Council
>
> This email, together with any attachment(s), is for the exclusive
> and confidential information of the addressee(s).  Any other distribution,
> use or reproduction without the sender's prior consent is unauthorised
> and strictly prohibited.  If you receive this message in error, please
> notify the sender immediately and delete the message without making any
> copies.
> Any views or opinions expressed are the author's and do not necessarily
> represent those of Highway Insurance.  You should not rely on the content
> of this message unless satisfied it is from an authorised source.
> Highway Insurance reserves the right to monitor all email communications.