outsystems best practices | SaveCore pattern the right way

I know that you’re going to tell me that everyone knows how to do a save core action and everyone knows how to use CreateOrUpdate entity action, but the truth is that I continue to see a lot of bad practices.

How we do it?

When we start to learn Outsystems the first pattern we learn is how to create a SaveCore action to encapsulate entity actions. This pattern uses the CreateOrUpdate entity action but this cannot be the right way to do it.

CreateOrUpdate action to save data into database

What you might not know is that CreateOrUpdate sends always two SQL scripts into the database (even when we need to insert a new record, the first statement sent to database is an update).

To show this behavior I used SQL Server Profile to see what SQL statements outsystems sends to the database.

First statement sent is an update statement

exec sp_executesql N’SET NOCOUNT OFF; 
UPDATE [StackOverflow2013].[dbo].[Badges] 
   SET [Name] = @ssName, 
   [UserId] = @ssUserId, 
   [Date] = @ssDate, 
   [CreatedOn] = @ssCreatedOn, 
   [CreatedBy] = @ssCreatedBy, 
   [UpdatedOn] = @ssUpdatedOn, 
   [UpdatedBy] = @ssUpdatedBy 
   WHERE [Id] = @ssENBadgesssId
’,N’@ssName varchar(10),
@ssUserId int,
@ssDate datetime,
@ssCreatedOn datetime,
@ssCreatedBy int,
@ssUpdatedOn datetime,
@ssUpdatedBy int,
@ssENBadgesssId int’,
@ssName=’New badge’,
@ssUserId=1,
@ssDate=’2022–01–01 00:00:00',
@ssCreatedOn=’2022–05–16 21:11:20',
@ssCreatedBy=1,
@ssUpdatedOn=’2022–05–16 21:11:20',
@ssUpdatedBy=1,
@ssENBadgesssId=0

First statement sent by Outsystems is and update statement with id = 0 that will not update any record, this operation takes 283 ms and SQL engines uses 166 reads to apply the update.

Second statement is the insert statement

exec sp_executesql N’SET NOCOUNT ON; 
INSERT INTO [StackOverflow2013].[dbo].[Badges] ( 
  [Name], 
  [UserId], 
  [Date], 
  [CreatedOn], 
  [CreatedBy], 
  [UpdatedOn], 
  [UpdatedBy] ) 
VALUES ( @ssName, @ssUserId, @ssDate, @ssCreatedOn, @ssCreatedBy, @ssUpdatedOn, @ssUpdatedBy ); 
SELECT SCOPE_IDENTITY();’,N’@ssName varchar(10),
@ssUserId int,
@ssDate datetime,
@ssCreatedOn datetime,
@ssCreatedBy int,
@ssUpdatedOn datetime,
@ssUpdatedBy int’,
@ssName=’New badge’,
@ssUserId=1,
@ssDate=’2022–01–01 00:00:00',
@ssCreatedOn=’2022–05–16 21:11:20',
@ssCreatedBy=1,
@ssUpdatedOn=’2022–05–16 21:11:20',
@ssUpdatedBy=1

The second statement sent to database is the insert statement needed to create the new record.

How we should do it?

To avoid send unnecessary scripts and overload database we redesign the SaveCore pattern to send only the necessary scripts to be executed.

SaveCore action redesigned

Only one script is sent to database

With this redesigned model only the necessary statement is sent to database. In a factory with a high load of new records this save can be significant and we can save a lot of time and database effort.