topic Slow SAS Enterprise Case Management Insert to DB speed in Fraud, AML and Security Intelligence

Slow SAS Enterprise Case Management Insert to DB speed

imdickson — Thu, 26 Sep 2019 02:09:18 GMT

Hi. I am a developer for SAS Enterprise Case Management and i've developed XML for the client to key-in data that has more than 50k thousands of records to insert into INCIDENT_UDF_NUM_VALUE and few thousands to INCIDENT_UDF_CHAR_VALUE. This is the problem, once my client done entering all data into ECM, the save button will take 30mins to run.

What i did was using SQL Profiler to trace what's going on when we click on the Save button. It insert data into ECM DB row by row with SQL Stored procedures. Every stored procedures takes 0.01 seconds to execute and insertion took another 0.01. Hence, every row of insertion takes 0.02 seconds and add them up for 60k records, it will take more than 20mins.

Is there a way for us to optimize this insertion? Understand that ECM is pre-built and cant be customized for this insertion unless there is any other advanced settings that can be done by SAS?

Seeking for your help on this matter.

Re: Slow SAS Enterprise Case Management Insert to DB speed

ElizabethKyria — Tue, 15 Oct 2019 10:36:34 GMT

Hi there

Trying to understand the exact situation, are you using SAS/ACCESS to insert into PostgreSQL?

Kind regards

Liz

Elizabeth Kyriacou

Business Operation Specialist

Fraud & Security Intelligence Division

Tel: + 44 (0)1628 490859 ▪ Mobile: + +44 (774) 1241863▪ elizabeth.kyriacou@sas.com

SAS ▪ 7^th Floor 199 Bishopsgate ▪ London, EC2M 3TY

www.sas.com/fraudfinancialcrime

SAS® … THE POWER TO KNOW®

Re: Slow SAS Enterprise Case Management Insert to DB speed

imdickson — Wed, 16 Oct 2019 02:49:47 GMT

Hi Liz,

It is configured to use ODBC as connector to connect to Microsoft SQL DB server, so if I understand correctly, it is SAS/ACCESS. It is not inserting into PostgresSQL but Microsoft SQL.

Re: Slow SAS Enterprise Case Management Insert to DB speed

ElizabethKyria — Wed, 16 Oct 2019 15:34:51 GMT

Hi there

Many thanks for clarifying, the appropriate resource from SAS should be assting with this shortly, however, if you are not getting the advice you need quickly enough, SAS Technical Support can also help!

Here is a link to opening a support track:

https://support.sas.com/en/technical-support/contact-sas.html

Regards

Liz

Re: Slow SAS Enterprise Case Management Insert to DB speed

jmac — Wed, 16 Oct 2019 18:13:09 GMT

Hi,

I would suggest to please open a tech support track as Liz had suggested as we would need information about your setup (e.g. SAS Enterprise Case Management version and hot fix levels, database version and etc.), as well as, the UI definition you have created.

We would need to review the UI definition to see what functions were used to invoke the updates to the database tables, so we can provide some suggestions.

Please find the link to opening a SAS technical support track below.

https://support.sas.com/en/technical-support/contact-sas.html

Regards.

Re: Slow SAS Enterprise Case Management Insert to DB speed

imdickson — Thu, 17 Oct 2019 05:39:39 GMT

Hi @jmac @ElizabethKyria . I did raise a SAS Technical Support track but it was closed saying this is not technical issue. My Technical support log number is : SAS 7612899201

I do hope that your team could further assist me as this seems to be my only hope to improve the speed.

Without looking at UI Definition file, can we look into the area how SAS ECM insert data into ECM Database (INCIDENT_UDF_CHAR_VALUE, INCIDENT_UDF_NUM_VALUE)? Based on my tracing findings, it seems like SAS ECM will generate stored procedures and execute it for every row in INCIDENT_UDF_CHAR/NUM_VALUE. Is there a way to improve this stored procedures and generate more rows of data in one shot? like bulk insert?

Re: Slow SAS Enterprise Case Management Insert to DB speed

imdickson — Fri, 18 Oct 2019 04:04:39 GMT

Hi @jmac @ElizabethKyria . I've provided my track number there. Is there anyway that we can improve the performance of this?