Short Description:
Inline Preprocessing was introduced in Saviynt IGA solution v5.4.2 or later to achieve the following goals:
- Avoid database locking caused by concurrent custom queries or resource-intensive database computing jobs on the SSM schema.
- Separate import jobs from DB computing jobs to prevent race conditions.
- Reduce dependency on explicit DB computation during provisioning by utilizing implicit data massaging during import.
- Segregate common business logic from provisioning activity to import activity for future use.
While inline processing brings significant benefits, it is essential to follow specific best practices to maximize its advantages within the Saviynt product.
Applicable Version:
All versions
Detail Best Practices:
a) Fetch the minimal data needed:
- Use specific column names rather than using "SELECT *" to avoid unnecessary data copying and increased network traffic.
- Example:
- Avoid: "USERS": "SELECT * FROM users"
- Prefer: "USERS": "SELECT userkey, firstname, lastname FROM users"
b) Index key columns:
- Explicitly add indexes on columns used in JOIN clauses to retrieve datasets faster.
- Index all columns used for joining tables in UPDATE statements within "PREPROCESSQUERIES."
- Example:
- "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" Note: Optimize performance by using joins instead of inner queries. TABLEINDEXES can be used to define indexes.
c) Once indexed, avoid SQL Functions:
- Avoid using functions on indexed columns as it undermines the effectiveness of indexes.
- Example:
- Good practice: "SELECT username FROM users WHERE firstname LIKE 'TU%'"
- Avoid: "SELECT username FROM users WHERE LEFT(customer_code, 2) = 'TU'" Using SQL functions on indexed columns can result in slower query performance.
- In the example if the "firstname" column is indexed, the query SELECT username FROM users WHERE firstname LIKE 'TU%' is considered a best practice because it directly uses the indexed column "firstname" without applying any SQL function. On the other hand, the query SELECT username FROM users WHERE LEFT(customer_code, 2) = 'TU' applies the "left" function on the indexed column "customer_code," which can potentially degrade the performance of the query.
- when you use SQL functions on an indexed column, such as using the "left" function in above example, it can undermine the effectiveness of the index. SQL functions manipulate or transform data, and when applied to an indexed column, the database system may not be able to utilize the index efficiently, resulting in slower query performance
d) Seamless Indexing across SSM tables and temp tables:
- Indexes in Saviynt tables and temp tables should match for optimized query operations.
- Use the same column types for joins across tables to improve performance.
e) Avoid ORDER BY clause:
- Since the inline preprocessor is used for backend data computing and massaging, not for displaying data in the UI, it's recommended to avoid using the ORDER BY clause.
f) Use LIMIT function when searching for a unique row:
- If querying CURRENT or NEWUSERDATA tables for a unique row, leverage the LIMIT function for enhanced performance. The system stops traversing the table or index after finding one unique record.
g) Utilize OOTB shipped DB functions: Saviynt IGA provides the following functions (available from v2022.0.1) for specific purposes:
- FN_EIC_REPLACE: Replace accented letters and special characters with alternatives for parsing.
- FN_EIC_SEQGEN: Generate a unique value each time it is invoked.
Key Benefit:
Faster user import jobs, improved query performance, and avoidance of performance issues or stuck queries.
Reference Documentation:
For detailed information and examples, refer to the documentation available at: https://docs.saviyntcloud.com/bundle/EIC-Admin-v2022x/page/Content/Chapter03-User-Management/User-Im...
