Optimizing for performance

SQL expression vs. SQL for update/new

The difference between the attributes “SQL expression” (so called SQL fields) and “SQL for update” and “SQL for new” (so-called automatic fields) is that the first type runs a SQL query every time data is accessed while “SQL for update/new” runs when the record is updated and created respectively.

The effect of this is that it is always faster to retrieve data from an automatic field compared to a SQL expression field. Therefore, SQL fields should be used with great caution, especially in lists with a lot of data. In this case, it is almost always preferable to use an automatic field. If the value is dependent on the object e.g. campaign card with fields that specify the number of participants who answered yes, use csp_afterupdate to update the field on the campaign card when updating the participant object.

Correct cursor type

When using cursors in stored procedures the parameters LOCAL STATIC READ_ONLY FORWARD_ONLY should almost always be specified. This is especially important in csp_afterupdate since the performance gain in a database with many updates can be a factor thousand or more. Example:

DECLARE @idcompany INT
DECLARE @name NVARCHAR(32)
 
DECLARE curTest CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
SELECT [idcompany], [name] FROM [company]
WHERE [status] = 2
 
OPEN curTest
 
FETCH NEXT FROM curTest INTO @idcompany, @name
 
WHILE @@FETCH_STATUS = 0
BEGIN
   --Do something
   FETCH NEXT FROM curTest INTO @idcompany, @name
END
 
CLOSE curTest
DEALLOCATE curtest