Context:
Inline processing was introduced on the Saviynt IGA solution v5.4.2 or later to perform the followings
- Avoid database locking due to multiple custom query or resource-intensive database computing jobs runs parallel on the SSM schema
- Segregate import job from DB computing job to avoid race conditions
- Reduce the dependency from explicit DB computation during provisioning to implicit data massaging during import
- Segregate certain common business logic from Provisioning activity to import activity for future usage
Even though overall inline processing brings substantial benefits for business, however, there are certain best practices step needs to be followed to get optimal benefits as designed in Saviynt product
BEST PRACTICES AND GUIDELINES :
- Fetch the minimal data that you need
- ADDITIONALTABLES : It helps to bring column data either from the identity store (Users) or specific target system account data from the Accounts table associated with an identity.
- Avoid SQL SELECT * from copying data from the existing SSM store as it increases network traffic
- Use SQL SELECT * when it is necessary. instead, leverage explicitly type column names that are required for business logic to compute
e.g.
“USERS”:”select * from users” --- Avoid this if whenever possible
“USERS”:”select userkey,firstname,lastname from users” – identify the column name as requires
2. Index key columns:
a. To retrieve faster datasets from the query, we need to explicitly add indexes on the columns which are used in JOIN clauses as temporary tables are created without indexing
b. UPDATE statement caters across multiple tables within “PREPROCESSQUERIES”, make sure to index all the columns which are used to join the tables
e.g.
"PREPROCESSQUERIES":
["alter table currentaccounts add index `idx_temp` (accountkey)",
"ALTER TABLE currentuser_accounts ADD INDEX `idx_temp` (`USERKEY` ASC, `ACCOUNTKEY` ASC)",
"ALTER TABLE currentusers ADD INDEX `USERNAME` (`USERNAME` ASC),ADD INDEX `idx_temp` (`USERKEY` ASC),ADD INDEX `idx_manager_temp` (`MANAGER` ASC)",
"ALTER TABLE NEWUSERDATA ADD INDEX `idx_temp_USERNAME` (`USERNAME` ASC)",
"UPDATE NEWUSERDATA n inner join currentusers u on u.username = n.username inner join currentuser_accounts uac on u.userkey=uac.userkey inner join currentaccounts ac on ac.accountkey= uac.accountkey SET n.customproperty32 = ac.name where ac.ENDPOINTKEY=39 and n.statuskey = 1",
"UPDATE NEWUSERDATA n join currentusers u on u.username = n.username SET n.SYSTEMUSERNAME = u.SYSTEMUSERNAME,n.customproperty32 = u.customproperty32,n.CUSTOMPROPERTY41 = u.CUSTOMPROPERTY41,n.email = u.email","]
3. Once Index, avoid SQL Functions :
Do not use functions over-indexed columns as the indexes lose their purpose.
e.g.
SELECT username FROM users WHERE firstname like ‘TU%’ ---- Best practices if username is index
Avoid leverage function such as ‘left’
SELECT username FROM users WHERE left (customer_code,2)=’TU’
4. Seamless Indexing across SSM tables and temp tables
To perform optimized query operation, indexes in SSM tables and temp table have to be the same. Use the same column types for joins across tables to get the better performance
Since we are using Saviynt Inline processor for backend business logic for data computing and data massaging, and not displaying data in UI, hence avoid the Order BY clause
6. Use LIMIT Function when looks for a unique row
If we are looking for one unique row when querying required Current or NewUserData tables, leverage the LIMIT function to enhance performance. The system stops traversing the entire table or index when it finds one unique record
7. Avoid MYSQL non-deterministic functions, like NOW() and RAND()
MYSQL can’t cache nondeterministic function, hence avoid using them to perform better query caching
