Disable SQL Customizations On Update¶

From lime-crm version 2.442.0 it's possible to disable database customizations on updates of Limeobjects. This will reduce the complexity and database traffic from Lime CRM Web Server when saving Limeobjects. The benefits are:

Faster save
Reduced risk of deadlocks in the database
Less load on the database

This feature can be enabled with the feature flag run_sql_customizations_on_update set to false. It will then disable these SQL customizations:

csp_beforeupdate, csp_afterupdate, csp_afterdataupdate and csp_afterupdatecompleted.
SQL for update.
Update if relation has changed.

Find SQL Customizations¶

Custom Stored Procedure¶

Check for these custom stored procedures in database and if they contain any customizations:

csp_beforeupdate
csp_afterupdate
csp_afterdataupdate
csp_afterupdatecompleted

SQL For Update¶

Find all SQL for update in database with this SQL query:

SELECT 
  t.[name] AS 'Limetype',
  f.[name] AS 'Property',
  ft.[name] AS 'Property Type',
  ad.[value] AS 'Formula'
FROM attributedata ad 
INNER JOIN field f ON f.idfield = ad.idrecord
INNER JOIN [table] t ON t.idtable = f.idtable
INNER JOIN fieldtype ft ON ft.idfieldtype = f.fieldtype
WHERE ad.[name] = 'onsqlupdate'
ORDER BY t.[name]

Update If Relation Has Changed¶

Find out if Update if relations has changed is default on for database with this SQL query:

-- See if it's default on for the database.
SELECT [name], [value] FROM setting
WHERE [name] = 'sys_update_changed_relations' AND [value] = 1

Find out if Update if relations has changed is on for different Limetypes with this SQL query:

-- This will only show where it's turned on.
-- If Limetype is using default for database it won't show here.
SELECT 
  t.[name] AS 'Limetype',
  f.[name] AS 'Property',
  ft.[name] AS 'Property Type',
  ad.[value] AS 'Formula'
FROM attributedata ad 
INNER JOIN field f ON f.idfield = ad.idrecord
INNER JOIN [table] t ON t.idtable = f.idtable
INNER JOIN fieldtype ft ON ft.idfieldtype = f.fieldtype
WHERE ad.[name] = 'updateonchanges' AND ad.[value] = 1
ORDER BY t.[name]

Move SQL Customizations¶

At the moment probably most SQL customizations should be moved to Custom Limeobjects. If there are some complex operations that don't need to be run directly, it's also possible to move the logic to a Task and trigger it to run in a Custom Limeobject. This will also improve performance on updates.

If the SQL customizations are used by integrations/add-ons that doesn't go trough the Lime CRM Web Server. It's possible to keep the SQL customizations for it to run by these integrations/add-ons. E.g. Mail Gateway, SSIS package or Lime Web Service.

Custom Stored Procedure¶

Edit or delete custom stored procedures in the database.

SQL For Update¶

Edit or delete SQL for update in Lisa. This is a field property.

Update If Relation Has Changed¶

Edit Update if relations has changed in Lisa. This is a database or tab relation property.