SQL Query to Update with good perfomance

You can simply use update from join syntax though with SQL Server 2008 you could also opt to use MERGE instead. The idea is that you create a table variable which will hold the info about the records affected by the transaction, so you can then insert them into the log table after the update is executed. I assume that the TransactionNo is unique in the SubTransaction, because if not then you might end up with updating the single record more than once, in which case some additional measures would be required for the update script to work properly. Here is the script:

begin tran;

-- declare table variable to hold the info about
-- the records in the MainTransaction table which
-- are subjected to the update statement
declare @updated table 
(
    TransactionNo varchar(64) not null,
    UpdatedTime datetime not null
);

-- this update is limited to only those records in the
-- MainTransaction table which have their SubTransaction
-- counterparts with changed Pay or Customer. It is safe
-- to just issue a plain set instead of worrying about the
-- case clauses in the update statement
update mt
set
    Pay = st.NewPay,
    Customer = st.NewCustomer
    output inserted.TransactionNo, getdate() into @updated
    from MainTransaction mt inner join SubTransaction st
        on mt.TransactionNo = st.TransactionNo
    where
        st.OldPay <> st.NewPay or st.OldCustomer <> st.NewCustomer;

-- insert info about affected records into your log table
insert into LogTable (TransactionNo, UpdatedTime)
select TransactionNo, UpdatedTime from @updated;

commit tran;
go

Hope this helps,

Oleg