x

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?
more ▼

asked Mar 13 '12 at 12:57 PM in Default

aRookieBIdev gravatar image

aRookieBIdev
2.3k 42 53 58

Which version of SQL Server?
Mar 13 '12 at 01:55 PM Blackhawk-17
I am using SQL Server 2008 R2
Mar 13 '12 at 01:56 PM aRookieBIdev
What indexes do you have on the tables?
Mar 13 '12 at 02:02 PM Blackhawk-17
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.
Mar 13 '12 at 02:16 PM aRookieBIdev
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?
Mar 13 '12 at 03:06 PM Sacred Jewel
show all comments (comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Mar 13 '12 at 04:11 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

this is brilliant .. Thanks Oleg
Mar 13 '12 at 05:44 PM aRookieBIdev
@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.
Mar 14 '12 at 06:18 AM Usman Butt

@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
Mar 14 '12 at 02:44 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x672
x562
x128

asked: Mar 13 '12 at 12:57 PM

Seen: 1202 times

Last Updated: Mar 14 '12 at 05:10 PM