While looking at the generated queries by Servoy that are sent to SQL Server, we came to the conclusion that no Database Transactions are started, but instead the "implicit transactions" setting for the current connection is toggled over and over again.
Mimicking the queries sent to the server in a test script to be ran from SQL Server Management Studio, and comparing this with a SQL statement that uses real named Database Transactions, we observe a difference in the execution time.
We have 2 questions on this:
- why has Servoy chosen to toggle the implicit transactions ("set implicit_transactions ON/OFF") instead of using a "Begin tran/commit tran" ?
- How can one avoid using this technique, as we see that in the version with implicit transactions takes about 15-20% more time than the version with explicit transactions?
This implies as well that nested transactions are made impossible.
test script used:
DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;
declare @nr_executions int = 10000;
--implicit transactions "Servoy style"
SET @t1 = GETDATE();
declare @cntr int = 1
while @cntr <= @nr_executions
begin
SET IMPLICIT_TRANSACTIONS ON
update search_criteria_user set sys_modificationdate= '2017-06-26 14:08:02.047' where search_criteria_user_id = N'51A41E60-FC45-432F-BEA6-F67B4B836D39'
IF @@TRANCOUNT > 0 COMMIT TRAN
IF @@TRANCOUNT > 0 ROLLBACK TRAN
IF @@TRANCOUNT > 0 COMMIT TRAN
SET IMPLICIT_TRANSACTIONS OFF
set @cntr += 1
end
SET @t2 = GETDATE();
SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;
-- Explicit transactions
SET @t1 = GETDATE();
set @cntr = 1
while @cntr <= 10000
begin
begin tran mytransactionname
update search_criteria_user set sys_modificationdate= '2017-06-26 14:08:02.047' where search_criteria_user_id = N'51A41E60-FC45-432F-BEA6-F67B4B836D39'
commit tran mytransactionname
set @cntr += 1
end
SET @t2 = GETDATE();
SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;