DB transactions on SQL Server

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

DB transactions on SQL Server

Postby pauwel.demeyer » Mon Jun 26, 2017 3:12 pm

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;
pauwel.demeyer
 
Posts: 4
Joined: Wed Jan 27, 2016 10:21 am

Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 8 guests

cron