Idea - Change CreateOrUpdate behaviour to avoid desnecessary updates

Change CreateOrUpdate behaviour to avoid desnecessary updates

New

Database

Hi,

Each time we use CreateOrUpdate entity action Outsystems send first one update statement and if this statement fails then sends the insert statement.

1. First statement send is an update

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


2. If this fails (because it's and insert) then Outsystems send an 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


This behavior doesn't make any sense because we are sending unnecessary scripts to be executed by database (first update takes some MS to be executed and take CPU time).

On factories with a high volume of inserts this have impact. 

Resolution:
If primary key is nullidentifier -> send insert statement to database
If primary key is filled -> send update statement

17 Jun 2022

Daniël Kuhlmann

MVP

Fully agree, the implementation is just not performant, and now we need to code an IF and two CRUD actions in our code wrappers...

18 Jun 2022

Matthias Preuter

MVP

Or the CreateOrUpdate should be implemented like this:

if exists (select top 1 1 from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert into table (key, ...)
   values (@key, ...)
end

or

update table with (serializable) set ...
where key = @key

if @@rowcount = 0
begin
      insert into table (key, ...) values (@key,..)
end

22 Jun 2022

MVP

I agree, hence our codeguide explicitly says, do not use the CreateOrUpdate.

@Matthias Preuter how is your code better? I admit, my sql is rusty ;)

your code seems to execute also 2 actions,

first one: a select then an insert or update.

second one: update and optional an insert

27 Jun 2022

Leonardo Fernandes

MVP

The current behavior would actually create a record even if the primary key is not null, if that record doesn't exist on the database. So I believe the suggestion from @Matthias Preuter is more appropriate and will be backwards-compatible.

Another alternative I found is to use MERGE statement, which is part of SQL 2008:

https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql#a-use-merge-to-do-insert-and-update-operations-on-a-table-in-a-single-statement

29 Aug 2024