Clarification about how transactions works

[mrjohn]

Hi.I have a silly question but I need a clarification:I have a .NET 3.5 application where data is loaded from saver in some datatables

something like this

Using conn As New FbConnection(...)
            conn.Open()
                    Using cmd As New FbCommand()
                        cmd.Connection = conn
                        cmd.Transaction = tr
                        cmd.CommandText = "SELECT .."
                        Using ad As New FbDataAdapter(cmd)
                            ad.Fill(table1)
                        End Using

                       For Each r As DataRow In table1.Rows
                                 .....
                        Next

                    End Using

               

                    Using cmd2 As New FbCommand()
                        cmd2.Connection = conn
                        cmd2.Transaction = tr
                        cmd2.CommandText = "SELECT .."
                        Using ad As New FbDataAdapter(cmd2)
                            ad.Fill(table2)
                        End Using

                        
                       For Each r As DataRow In table2.Rows
                                 .....
                        Next


                    End Using

..................
            End Using 

there is no transaction defined for above connection,but adding one and measuring execution time,the query works faster:

Using conn As New FbConnection()
            conn.Open()
            Using tr As FbTransaction = conn.BeginTransaction
                    Using cmd As New FbCommand()
                 .............
                tr.Commit()
            End Using

the silly question is: why transaction give a better performance in this case as log the above querys could not be execute on the server at the same time,as code above after first query execution data is locally used and then query2 is executed
thanks for any clarification

[techgnome]

If you're getting better execution on a select query in a transaction one of two thing is happening:
1) it's an illusion
2) you're accessing data in an existing transaction

your comments lead me to believe that #1 is more likely.

Unless you're accessing data that was written to tables in a transaction that hasn't been committed yet, there is no reason to use a transaction when executing a select statement. It's creating overhead and locks that are going to potentially block other users during the read cycles. Actually, that's probably why it is faster, it's creating a lock on the data, so it doesn't need to worry about someone else writing to it at the same time, so it's able to read it faster. But that's also part of the problem... that means it's going to be locked for any one else to read or write. Generally best practice is to only lock data just before you write to it and unlock it as soon as you're done. Since there isn't a need to lock it when reading it, that would be considered bad form.

-tg

[mrjohn]

thanks!