toc

Table of Contents

maxLevel	2
style	square

Server Configuration and Sizing

...

Connect to the Tomcat Manager, click Server Status in the upper right corner.
In the Server Status screen, check the memory user by TOMCAT: Free memory, Total memory, Max memory

For Semarchy v4.xxDM:

Maximum Memory -Xmx value must be at least 4 GoGb.
The OS should have at least 8 Go Gb of RAM.

Database Server

Server Requirements

...

Info

Here is an example of troubleshooting an overmatch situation on phone number. It allowed us to identify that there was a placeholder value in the standardized phone number that creates a massive cartesian product.

Run the query below to learn how many records were inserted into the DU_CONTACT table by the previous matching rules. Compare it with the current record count of the MI_CONTACT.

This query should allow us to pinpoint any oddities in the MI_CONTACT table:

select STD_PRIMARY_PHONE, count(*) bin_size
from MI_CONTACT
group by STD_PRIMARY_PHONE
order by 2 desc

...

Match Rules Issues

The following issues are a common source of performance in the matching process.

Using Transformations in Matching Rules
Avoid functions transforming data (SOUNDEX, UPPER, etc. included) in match/binning rules.
- Reasons:
  - May cause an issue on the Indexes. These functions is are performed for every time the record is compared.
- Solution
  - Materialize these values into attributes via enrichers
Use Fuzzy Matching Functions with Care
- Distance and Distance Similarity functions are the most costly functions.
  Sometimes, materializing a phonetized value with enricher then comparing with equality gives functionally equivalent results.
Very Large Complex Rules
- Avoid one big matching rule.
- Each rule should address a functionnally functionality consistent with a set of attribute data.
Consider IndexingFor very large volumes, adding an index on the significant columns involved in the binning, then one index for the columns matching rule.
e.g.:
create index USR_<indexName> on MI_<entity> (B_BATCHID, B_BRANCHID, B_CLASSNAME, B_PUBID, B_SOURCEID, <columns involved matching, with those having more distinct values first>);
-- Remove BranchID for v4.0 and above
Avoid joining to parent entities
- If you have large data sets, you may have very bad performance if your match rule looks up the parent entity to find attribute values for matching.
- For example, this match rule Record1.Account.CustomerNumber = Record2.Account.CustomerNumber took 4 hours to run for 1 million records. When we updated the child entity to enrich the CustomerNumber from the parent into an attribute on the child entity, the match time went down to 2 minutes. Another option is to use Record1.FID_Account = Record2.FID_Account if it satisfies business requirements.
Consider Indexing
- For very large volumes, adding an index on the significant columns involved in the binning, then one index for the columns matching rule.
  e.g.:
  create index USR_<indexName> on MI_<entity> (<columns involved matching, with those having more distinct values first>, B_PUBID, B_SOURCEID, B_CLASSNAME);
- Have you done truncate and full reload operations recently?
  One of the standard maintenance operations, in this case, is to rebuild all of the indexes with:
  alter index [index name] rebuild online; /* Oracle syntax */
  The code to do this for all indexes can be easily generated using a select on the user_indexes view.
- To rebuild the indexes on the entire database in PostgreSQL, you can use the Postgres statement
```
REINDEX DATABASE database_name; /* PostgreSQL syntax */
```
  If you need to just reindex a specific table you can use
  REINDEX TABLE table_name; /* PostgreSQL syntax */

Issues in Other Certification Phases

Consolidation

Symptom: "ORA-01467: Sort Key too long" issue
- Solution: alter session set
  use the Postgres statement
```
REINDEX DATABASE database_name; /* PostgreSQL syntax */
```
  If you need to just reindex a specific table you can use
  REINDEX TABLE table_name; /* PostgreSQL syntax */
Detect over matching possibilities:
- To detect potential huge clusters, this query should let you know if your query needs to do more exact matching for binning to reduce cluster sizes when performing exact matches (replace the column names with the columns you are matching on):
```
/* For SQL Server */
select stdaddress1, stdzip_code, count(*) as cnt
from mi_contact
group by stdaddress1, stdzip_code
order by count(*) desc
```
- Use this query if you're comparing cluster sizes and total number of calls to SEM_EDIT_DISTANCE:
```
/* For SQL Server */
select sum(cnt * cnt)
from (
select stdaddress1, stdzip_code, count(*) as cnt
from mi_contact
group by stdaddress1, stdzip_code
) derived
```

SEM_EDIT_DISTANCE_SIMILARITY and SEM_EDIT_DISTANCE functions on SQL Server performs poorly (as of v5.2):

If you are using the SEM_EDIT_DISTANCE_SIMILARITY or SEM_EDIT_DISTANCE functions in your match rules, it could be performing poorly on SQL Server. One of our customer migrating from PostgreSQL to SQL Server was facing huge performance regression during the migration (
Jira Legacy
server System JIRA
serverId 7db41d32-f010-38fc-b966-a5c06dc46fba
key SUPPORT-9524
).

The workaround he found is interesting:

He found a CLR procedure on the web for the Levenshtein distance calculation (in this case, this one : https://github.com/DanHarltey/Fastenshtein)
He has compiled the c# code in a dll and install it on the SQL Server database

He has created a database function with this script :

Code Block

sp_configure 'clr enabled',1
RECONFIGURE
EXEC sp_configure 'show advanced options',1;
RECONFIGURE;

EXEC sp_configure 'clr strict security',0;
RECONFIGURE;

CREATE ASSEMBLY FastenshteinAssembly FROM 'E:\INTEND_INSTALL\CLR\Fastenshtein.dll' WITH PERMISSION_SET = SAFE

CREATE FUNCTION SEM_LEVENSHTEIN_CUST(@value1 [nvarchar](MAX),@value2 [nvarchar](MAX))
RETURNS[int]
AS
EXTERNAL NAME[FastenshteinAssembly].[Fastenshtein.Levenshtein].[Distance]
GO

Then, he has declared SEM_LEVENSHTIEN_CUST in xDM and has used it in the match rules, instead of native SEM_EDIT_DISTANCE function.
Results : the Matching rule execution is completed in a few seconds instead of many minutes (for only 4 000 records)

SEM_NGRAMS_SIMILARITY function on SQL Server performs poorly (as of v5.0):

If you are using the SEM_NGRAMS_SIMILARITY function in your match rules, it could be performing poorly on SQL Server due to the lack of support for native matching functions in SQL Server. Hilaire came up with an alternative version of this function that should allow you to process the same number of calls roughly 10 times faster (same algorithm, but less nested function calls and optimized code)

Compile the function and note that there is the USR_ prefix. Call the function USR_SEM_NGRAMS_SIMILARITY directly in the match rules.

Code Block

/* For SQL Server only: 10x faster execution of the SEM_NGRAMS_SIMILARITY on SQL Server */

create  or alter function [dbo].[USR_SEM_NGRAMS_SIMILARITY]
(
   @P_STRING1 nvarchar(max),
   @P_STRING2 nvarchar(max),
   @P_NGRAM_LEN int = 2
)
RETURNS int
AS
BEGIN
DECLARE @C1 int, @C2 int, @RES int
DECLARE @P_TAB1 SEM_SPLIT_TBL, @P_TAB2 SEM_SPLIT_TBL
    INSERT INTO @P_TAB1 SELECT * from [dbo].SEM_SPLIT_NGRAMS(@P_STRING1, @P_NGRAM_LEN);
Set @C1 = @@ROWCOUNT
    INSERT INTO @P_TAB2 SELECT * from [dbo].SEM_SPLIT_NGRAMS(@P_STRING2, @P_NGRAM_LEN);
Set @C2 = @@ROWCOUNT
select @RES = case when @C1 = 0 or @C2= 0 then 0 else cast(2.0 * 100 * count(*) / (@C1 + @C2) as int) end
from @P_TAB1 L1 inner join @P_TAB2 L2 on L1.STR = L2.STR
    RETURN @RES;
END

GO

To estimate the time required to execute the step, you can compute the theoretical number of calls to SEM_NGRAMS_SIMILARITY using the binning expression of your first matching rule based on SEM_NGRAMS_SIMILARITY as follows:
```
select sum (CNT * CNT) as NB_CALLS 
from (
select STD_PRIMARY_PHONE, count(*) as CNT
from MI_CONTACT
group by STD_PRIMARY_PHONE
) derived
```

Issues in Other Certification Phases

Consolidation

Symptom: "ORA-01467: Sort Key too long" issue

Solution: alter session set "_windowfunc_optimization_settings" = 128; in session initializing for the connection pool in the datasource configuration.

Example:

Code Block

language	xml

<Resource name="jdbc/SEMARCHY_MDM" 
  username="DATA_LOCATION_USER" password="DATA_LOCATION_PASSWORD" 
  url="jdbc:oracle:thin:@rdmagdev.lcke3gss.eu-west-1.rds.amazonaws.com:1521:ORCL" auth="Container"
  factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
  maxActive="8" maxIdle="8" minIdle="0" maxWait="15000" initialSize="1" defaultAutoCommit="false" 
  validationQuery="select 1 from dual" testOnBorrow="true" 
  logValidationErrors="true" timeBetweenEvictionRunsMillis="45000" validationInterval="60000" 
  initSQL="alter session set "_windowfunc_optimization_settings" =

128; in session initializing for the connection pool in the datasource configuration.

 128" 
/>

Publish Certified Golden Data

...

Versions Compared

Old Version 30

New Version Current

Key

Server Configuration and Sizing

Database Server

Server Requirements

Match Rules Issues

Issues in Other Certification Phases

Consolidation

Issues in Other Certification Phases

Consolidation

Publish Certified Golden Data

Page Comparison

Versions Compared

Old Version 30

New Version Current

Key

Server Configuration and Sizing

Database Server

Server Requirements

Match Rules Issues

Issues in Other Certification Phases

Consolidation

Issues in Other Certification Phases

Consolidation

Publish Certified Golden Data