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.