Usage Note 19306: The DISTINCT clause in the riskranking SQL query might cause performance issues
 |  |  |  |  |
Riskranking queries use a DISTINCT clause that might cause slow performance if the Core and Knowledge Center are deployed on different databases and are linked via the ODBC driver. The ODBC link might not pass the DISTINCT clause, thus degrading performance of the query. In general, SAS® Anti-Money Laundering 2.1 does not support this database configuration.
The code has been revised to alleviate this problem. The original code places the DISTINCT clause inside the SELECT statement, as follows:
proc sql noprint;
connect to &dbkcflavor as &dbkcflavor (user=&kcDBUser
password=&kcDBUserPwd &kcDBConnOpts);
create table postDBMS2 as
select * from connection to &dbkcflavor
(select distinct alert.primary_entity_number,
pxref.party_number as PTY_number
from &kcSchema..fsk_temp_number alert,
&coreSchema..FSC_PARTY_DIM pxref,
&coreSchema..FSC_ACCOUNT_DIM axref,
&coreSchema..FSC_PARTY_ACCOUNT_BRIDGE pbridge
where alert.primary_entity_number =
axref.account_number and
axref.account_key = pbridge.account_key and
pbridge.role_key = 1 and
pbridge.party_key = pxref.party_key and
axref.change_current_ind = 'Y' and
pbridge.change_current_ind = 'Y'
);
quit;
%rcset(&sqlrc);
%if &trans_rc eq 3 or &trans_rc ge 5 %then %do;
%let _amlmsg_=ERROR: AML: Unable to retrieve primary parties.;
%put_aml_error;
%goto MACRO_END;
%end;
The revised code places the DISTINCT clause outside the SELECT statement. A macro (SASDISTINCT) is used to toggle the DISTINCT clause in the SQL procedure statement.
/* S0357516 */
%if not %symexist(SASDISTINCT) %then %let SASDISTINCT=N;
%if %upcase(&SASDISTINCT) ne Y %then
%do;
proc sql noprint;
connect to &dbkcflavor as &dbkcflavor (user=&kcDBUser
password=&kcDBUserPwd &kcDBConnOpts);
create table postDBMS2 as
select * from connection to &dbkcflavor
(select distinct alert.primary_entity_number,
pxref.party_number as PTY_number
from &kcSchema..fsk_temp_number alert,
&coreSchema..FSC_PARTY_DIM pxref,
&coreSchema..FSC_ACCOUNT_DIM axref,
&coreSchema..FSC_PARTY_ACCOUNT_BRIDGE pbridge
where alert.primary_entity_number = axref.account_number
and axref.account_key = pbridge.account_key and
pbridge.role_key = 1 and
pbridge.party_key = pxref.party_key and
axref.change_current_ind = 'Y' and
pbridge.change_current_ind = 'Y'
);
quit;
%rcset(&sqlrc);
%if &trans_rc eq 3 or &trans_rc ge 5 %then
%do;
%let _amlmsg_=ERROR: AML: Unable to retrieve primary parties.;
%put_aml_error;
%goto MACRO_END;
%end;
%end;
%else
%do; /* SASDISTINCT=Y */
%put;
%put ***********************************************;
%put NOTE: AML: WORK.POSTDBMS2 using SASDISTINCT=Y ;
%put ***********************************************;
%put;
proc sql noprint;
connect to &dbkcflavor as &dbkcflavor (user=&kcDBUser
password=&kcDBUserPwd &kcDBConnOpts);
create table postDBMS2 as
select DISTINCT * from connection to &dbkcflavor
(select alert.primary_entity_number,
pxref.party_number as PTY_number
from &kcSchema..fsk_temp_number alert,
&coreSchema..FSC_PARTY_DIM pxref,
&coreSchema..FSC_ACCOUNT_DIM axref,
&coreSchema..FSC_PARTY_ACCOUNT_BRIDGE pbridge
where alert.primary_entity_number = axref.account_number
and axref.account_key = pbridge.account_key and
pbridge.role_key = 1 and
pbridge.party_key = pxref.party_key and
axref.change_current_ind = 'Y' and
pbridge.change_current_ind = 'Y'
);
quit;
%rcset(&sqlrc);
Select the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS Anti-Money Laundering Alert Generation Server | Microsoft Windows XP Professional | 2.1 | |||
| Microsoft Windows NT Workstation | 2.1 | |||||
| Microsoft Windows 2000 Server | 2.1 | |||||
| Microsoft Windows 2000 Datacenter Server | 2.1 | |||||
| Microsoft Windows 2000 Advanced Server | 2.1 | |||||
| Linux | 2.1 | |||||
| Solaris | 2.1 | |||||
| 64-bit Enabled Solaris | 2.1 | |||||
| Microsoft Windows 2000 Professional | 2.1 | |||||
| 64-bit Enabled AIX | 2.1 | |||||
| AIX | 2.1 | |||||
A fix for this issue for SAS Anti-Money Laundering 2.1 is available at:
http://www.sas.com/techsup/download/hotfix/aml21.html#019306| Type: | Usage Note |
| Priority: | high |
| Date Modified: | 2007-05-28 00:03:33 |
| Date Created: | 2007-01-08 16:34:44 |