asked
SQL Query to Update with good perfomance
Following is my scenario where i am trying to do this the best possible way , i tried in SSIS using lookups but dint work out properly (low perfomance) Table Name: Subtransaction Fields : TransactionNo , old pay , new pay , old customer , new customer based on this data i need to update the transaction table with millions of records Table Name :MainTransaction Fields: TransactionNo,Pay,Customer I need to update the records matching on the transaction There are few conditions though. 1.pick up those records from the subtransaction where old pay and new pay are not the same , then update those records in the main transaction based on the transaction no 2.similarly pick up those records from the subtransaction where old customer and new customer are not same , then update those records in the main transaction based on the transaction no Now i need to insert the transaction nos that were updated in to a log table LogTable TransactionNo,UpdatedTime Can some one help me with this please?
It is a bit dodgy that you have old customer, new customer in one table and Customer in the other. And you still want to do the update based upon TransactionNo? Is TransactionNo Unique in both?
I have an index created on the TransactionNo just to do this update. But pls note the transactionno is a varchar(64) I will be deleting the index after the update.
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
@Oleg Netchaev +1. I always prefer this method for such scenarios. But I guess the steps to insert the data in declared table and then in the physical table could easily be eliminated. Instead the OUTPUT could be directly inserted into the Log table. This way the query could gain a better performance.
@Usman Butt Thank you and yes, not including the table variable in the mix could and would gain a better performance. I just like playing with table variables while the script is not finalized just to confirm that it works properly, specifically when there is a possibility that the join produces more than one match per main record. If, say, there are 2 unique records in the main table each having 3 matches in the sub table, then the number of affected records is still 2, though the update restated as a select using the same join produces 6 records. Which values from the existing combinations are picked depends on many factors so additional measures are then needed to tune the query to produce desired update (always doable). @aRookieBIdev Usman's recommendation makes sense, which means that you might want to consider removing the line declaring a table variable, remove the final insert into LogTable and replace the line reading output inserted.TransactionNo, getdate() into @updated in the update statement with direct insert into LogTable: output inserted.TransactionNo, getdate() into LogTable
5 People are following this question.
