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>
Subject: Re: Performance problems in V8.2
Content-Type: text/plain; charset=ISO-8859-1
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
from proclib.payroll p
where exists (select *
from staff s
where idnum in (select idnum
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
> 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?
> Stephen Dunn
> Highway Insurance
> 171 King's Road
> 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
> 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.