CODEWITHSUNDEEP
sqlperformancemariadb
zirt
zirt

Reputation: 21

Most performant way to update rows on large tables

I have 2 tables:

Request:

idㅤㅤ|ㅤㅤaccountㅤㅤㅤㅤ|ㅤsubaccountㅤ|ㅤamountㅤ|ㅤㅤupdatedAmount    

1ㅤㅤ|ㅤㅤACCOUNT_1ㅤㅤ|ㅤㅤㅤ12ㅤㅤㅤ|ㅤㅤ10.2ㅤㅤ|ㅤㅤnull       
2ㅤㅤ|ㅤㅤACCOUNT_2ㅤㅤ|ㅤㅤㅤ45ㅤㅤㅤ|ㅤㅤ0ㅤㅤㅤ|ㅤㅤnull          
3ㅤㅤ|ㅤㅤACCOUNT_3ㅤㅤ|ㅤㅤㅤ11ㅤㅤㅤ|ㅤㅤ50.6ㅤㅤ|ㅤㅤnull

DailyData:

idㅤㅤ|ㅤㅤaccountㅤㅤㅤㅤ|ㅤsubaccountㅤ|ㅤamountㅤ

1ㅤㅤ|ㅤㅤACCOUNT_1ㅤㅤ|ㅤㅤㅤ45ㅤㅤㅤ|ㅤㅤ3.12     
2ㅤㅤ|ㅤㅤACCOUNT_1ㅤㅤ|ㅤㅤㅤ12ㅤㅤㅤ|ㅤㅤ15        
3ㅤㅤ|ㅤㅤACCOUNT_2ㅤㅤ|ㅤㅤㅤ11ㅤㅤㅤ|ㅤㅤ1.09     
4ㅤㅤ|ㅤㅤACCOUNT_2ㅤㅤ|ㅤㅤㅤ70ㅤㅤㅤ|ㅤㅤ30.6     
5ㅤㅤ|ㅤㅤACCOUNT_3ㅤㅤ|ㅤㅤㅤ11ㅤㅤㅤ|ㅤㅤ50.6

I need to update the column updatedAmount in the Request table, meeting this criteria:

-It updates a row only if it finds a row in the DailyData table with the same account AND subaccount.
-It only updates if the value on the column amount is different in each table.

For the rows where this criteria meets, it saves the amount (from DailyData table) in the updatedAmount (Request table) column.

So for this example we would have this output, only the id 1 updates:

idㅤㅤ|ㅤㅤaccountㅤㅤㅤㅤ|ㅤsubaccountㅤ|ㅤamountㅤ|ㅤㅤupdatedAmount    

1ㅤㅤ|ㅤㅤACCOUNT_1ㅤㅤ|ㅤㅤㅤ12ㅤㅤㅤ|ㅤㅤ10.2ㅤㅤ|ㅤㅤ15              
2ㅤㅤ|ㅤㅤACCOUNT_2ㅤㅤ|ㅤㅤㅤ45ㅤㅤㅤ|ㅤㅤ0ㅤㅤㅤ|ㅤㅤnull                    
3ㅤㅤ|ㅤㅤACCOUNT_3ㅤㅤ|ㅤㅤㅤ11ㅤㅤㅤ|ㅤㅤ50.6ㅤㅤ|ㅤㅤnull

My big problem is the performance. This is a daily process, and the Request table will have + than 1 million rows, while the DailyProcess table is around 300k rows that change every day. So far I created 2 methods but both take a lot of time.

1:

UPDATE Request r                                              
LEFT OUTER JOIN DailyData d                                              
ON r.account = d.account AND r.subaccount = d.subaccount                                              
SET r.updatedAmount = d.amount

2:

UPDATE Request r, DailyData d                                              
SET r.updatedAmount = d.amount                                              
WHERE r.account = d.account AND r.subaccount = d.subaccount AND r.amount <> d.amount

Can someone suggest any faster method? Any help would be very much apreciated

Upvotes: 2

Views: 619

Answers (1)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

I mean you can use INNER JOIN

UPDATE Request r                                              
INNER JOIN DailyData d                                              
    ON r.account = d.account AND r.subaccount = d.subaccount                                              
SET r.updatedAmount = d.amount
WHERE r.amount <> d.amount;

The update performance can be increased by create index (account, subaccount) for both tables

Upvotes: 2

Related Questions