Slow SAS Enterprise Case Management Insert to DB speed

[imdickson]

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.

[ElizabethKyria]

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®

[imdickson]

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.

[ElizabethKyria]

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

[imdickson]

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

[jmac]

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.

[imdickson]

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?