0

I'm working on an asp.net web application project.

I tried the same operation with SqlTransaction and without SqlTransaction. Execution time of code which is using SqlTransaction is faster than that which is not using SqlTransaction.

I want to know the reason behind it, please let me know how SqlTransaction affects performance.

Both Code perform same Action with and withput SqlTransaction. 

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    SqlCommand command = connection.CreateCommand();
    SqlTransaction transaction;

    // Start a local transaction.
    transaction = connection.BeginTransaction("SampleTransaction");

    // Must assign both transaction object and connection 
    // to Command object for a pending local transaction
    command.Connection = connection;
    command.Transaction = transaction;

    try
    {
        command.CommandText =
            "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
        command.ExecuteNonQuery();
        command.CommandText =
            "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
        command.ExecuteNonQuery();

        // Attempt to commit the transaction.
        transaction.Commit();
        Console.WriteLine("Both records are written to database.");
    }
    catch (Exception ex)
    {
        Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
        Console.WriteLine("  Message: {0}", ex.Message);

        // Attempt to roll back the transaction. 
        try
        {
            transaction.Rollback();
        }
        catch (Exception ex2)
        {
            // This catch block will handle any errors that may have occurred 
            // on the server that would cause the rollback to fail, such as 
            // a closed connection.
            Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
            Console.WriteLine("  Message: {0}", ex2.Message);
        }
    }
}
pixelmeow
  • 654
  • 1
  • 9
  • 31
Brijraj
  • 177
  • 2
  • 3
  • 13
  • 2
    post your code please – AcidJunkie Jun 25 '14 at 10:28
  • 1
    I'd say this depends on your test method. Simply running the same action w/o transaction only one time doesn't tell you much about the actual performance. – Filburt Jun 25 '14 at 10:29
  • In within same Transaction I'm calling a Stored Procedure 500 times. – Brijraj Jun 25 '14 at 10:32
  • From memory using a transaction in ADO.Net simply emits a "begin transaction" call to the DB before your code is executed, so I'd be surprised if it was speeding things up. I suspect either your DB was under more strain when testing without the transaction, or you've made a mistake in how you benchmark this. – Daniel Kelley Jun 25 '14 at 10:33
  • I tested it multiple times. – Brijraj Jun 25 '14 at 10:34
  • Its "not that simple" as just calling the same test. Run this query on the db (if its sql server). SELECT plan_handle, st.text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st GO .. Also see : http://msdn.microsoft.com/en-us/library/ms174283.aspx – granadaCoder Jun 25 '14 at 17:44

0 Answers0