Date: Thu, 27 Sep 2001 15:12:27 +0100
Reply-To: Stephen Dunn <stephen.dunn@HIGHWAY-INSURANCE.CO.UK>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Stephen Dunn <stephen.dunn@HIGHWAY-INSURANCE.CO.UK>
Subject: Performance problems in V8.2
Content-Type: text/plain; charset="iso-8859-1"
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.
