August 16, 2006 at 9:34 am
Hi,
I was wondering if there are any pros and cons of using begin tran and commit tran in ADO.NET vs. having it in your T-SQL in a stored proc. Personally I prefer to have it in the stored proc.
Thanks,
Chris
August 17, 2006 at 4:08 am
Well, the BEGIN TRAN and COMMIT TRAN in ADO.NET has to do additional round trips to the server.
Additionally, doing it in ADO unfortunately makes it too easy to keep your transactions open for a long time.
I would agree with putting them in SPs are better. Then when modifying data only use ADO for launching your SPs.
Hanslindgren
August 17, 2006 at 8:45 am
I would say use transactions in ADO (or any other business logic layer) when you do several consecutive action calls against the database and need to bracket them in one transaction.
August 29, 2006 at 1:13 pm
That is correct, there is not much difference between ADO.NET transactions and T-SQL transaction because both are technically none atomic transactions because of T-SQL support for nested transaction. But for complex long running transactions it is more efficient to do it with ADO.NET because objects are garbage collected while structs are not. Now lets not confuse ADO.NET transactions with System.Transactions, those if not passed to T-SQL transaction will be promoted to distributed transaction by SQL Server because of explicit none atomic transactions. Read up on transaction savepoints because a T-SQL transaction without savepoint can rollback to one from any number and you can rollback savepoint if needed as of SQL Server 2000 service pack 3. Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
August 29, 2006 at 4:53 pm
I would like to get some clarification of this. How can you say that ADO.NET transactions are more effective then transactions in pure T-SQL? Are you saying that, for example, a .NET codepath using ADO.NET with MULTIPLE SQL Server DB calls encapsulated in one ADO.NET transaction can be more efficient then the use of a SP inside SQL Server that contains the transaction? Not only are you eliminating a lot of round trips with using the T-SQL approach but you neither need to instantiate any additional objects in .NET. How can a non-existent need (T-SQL approach) be worse then the need (albeit efficient) of a garbage collector in a .NET language?
Interested in learning more about this fascinating idea!
Hanslindgren
August 29, 2006 at 6:20 pm
ADO.NET transactions is more efficient because you can use one code block to do a lot and it implements IDisposable so you can call dispose before you start the code block with the second Using statement which calls dispose for you automatically. I don't have any thing against T-SQL transactions but tools are used based on needs. We all know SQL Server procedure cache uses the least used Algorithm which means if a transaction block it not used often users will have to wait for it to compile next time it is called you don't have that with ADO.NET transactions. Hope this helps.
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction_members.aspx
http://davidhayden.com/blog/dave/archive/2005/10/15/2517.aspx
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
August 30, 2006 at 10:01 am
Okay, then I think we understood the poster in two different ways.
The references you gave described only the .NET way of handling transactions. While I think the poster meant having everything inside a stored procedure you assume that he meant either using ADO.NET or using SqlTransactions.NET.
Then I can understand a little about your reasoning but from the wrong view point.
Using T-SQL I meant something like:
CREATE PROCEDURE myProcedure (@Param1 INT,@Param2 VARCHAR(20)) AS
SET NOCOUNT OFF
BEGIN TRANSACTION
<do smthng statement>
<do smthng statement>
<do smthng statement>
<do smthng statement>
COMMIT TRANSACTION
inside the SQL Server Engine and NOT using SqlTransaction inside .NET
Hanslindgren
August 30, 2006 at 12:31 pm
No he just said he prefered stored procs, but he was asking for both and you are assuming the SQL Server procedure cache will keep your stored proc in the procedure cache all the time. If stored procs are in the cache all the time then performance tuning companies and consultants will not be in business. I don't use either my work uses only objects and use the singleton pattern with thread locks.
And SqlTransaction is ADO.NET 2.0, maybe you mean System.Transaction those are not ANSI SQL compliant so SQL Server promotes them to distributed transactions but Oracle uses them very well because you install MTS(microsoft transaction server) in most Windows Oracle installations of 10g. System.Transaction was the old COM+ transaction, you cannot use them with MySQL and SQL Server usually promote the operation to distributed transactions.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
August 30, 2006 at 2:53 pm
In general I will use transactions inside an stored procedure that requires a transaction to deliver consist and valid results. For example, a proc that just does an update doesn't need an explicit transaction. If I'm calling multiple stored procs to achieve a result my first try would be to package them into a super proc and use a transaction to group them together, like this:
superproc
start trans
call proc1
call proc2
etc
end trans
Remember you want to be in and out of transactions fast, making round trips is not a good formula for doing that. As a rule try to have all transactions require one round trip to the server. If you start to need to do anything other than that you can look at .Net transactions.
August 30, 2006 at 8:40 pm
Hi Andy,
Can you please explain why you think a single update proc doesn't need an explicit Transaction?
Your super proc is a good example but I guess I would probably still have an explicit Transaction in each of those procs if they were to Insert,Update or Delete and check the Tran Count in each proc so that they could be used alone and still Create an explicit Transaction if the tran count was less then 1 but then again I am still a noobie.
Thank You,
Chris Lane
August 31, 2006 at 4:10 pm
Yes, but by 'both' he probably meant using only multi statement Stored Procedures in T-SQL (just using .NET by calling the SPs) compared to using .NET objects to do multiple single T-SQL statements.
Even if the SP is not in the SQL Server procedure cache it will compile very quickly. This compilation (when necessary) will probably be much faster then the extra round trips to the server you need to do with the .NET approach.
I believe that if you compare multiple single T-SQL statements, nested in transactions, inside .NET (even if you solve it by singleton pattern and threadhandling) will be slower then one single .NET call to a pure T-SQL multistatement stored procedure (and there is not even any need for any GC here at all!).
Anyone else agrees or have I got it totally wrong?
Regards,
Hanslindgren
September 1, 2006 at 6:07 am
If you look at 'autocommit mode' in BOL, you will find the following:
"Autocommit mode is the default transaction management mode of the SQL Server Database Engine. Every Transact-SQL statement is committed or rolled back when it completes. If a statement completes successfully, it is committed; if it encounters any error, it is rolled back. A connection to an instance of the Database Engine operates in autocommit mode whenever this default mode has not been overridden by either explicit or implicit transactions. Autocommit mode is also the default mode for ADO, OLE DB, ODBC, and DB-Library.
A connection to an instance of the Database Engine operates in autocommit mode until a BEGIN TRANSACTION statement starts an explicit transaction, or implicit transaction mode is set on. When the explicit transaction is committed or rolled back, or when implicit transaction mode is turned off, the connection returns to autocommit mode."
I don't think adding explicit transactions hurts anything, but I've never tried to measure to see if it did either.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply