Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MySQL (MariaDB) Update in Batches and loop until done?

Posted on 2016-07-14
8
74 Views
Last Modified: 2016-08-02
I have a couple of massive tables (T1 = 132,343,644 and T2 = 87,537,041) I need to update fields in T1 from field values in T2. Both have common 'recordID' fields that are indexed on each table.

Is there a way to run an update query in a batch type method so that its taking like 10,000 or 100,000 records at a time to update and then loop and grab the next batch until done. I have a feeling that this would be more efficient then my current update query.

UPDATE MASTER AS T1
INNER JOIN `DATE-TYPE_07042016` AS T2 ON T1.recordID = T2.recordID
SET T1.DATE = T2.DATE, T1.NEW_USED = T2.NEW_USED_CODE
WHERE T1.NEW_USED IS NULL

Open in new window



Server is CENTOS 7.2.1511
CPU : 8 vCPU
Memory : 32,768
0
Comment
Question by:FirstDirect
  • 5
  • 3
8 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 41711372
The way you have seems efficient assuming that there are indexes on the recordid's. If you want to do batches, you could either create partitions or simply use a where clause, like this:

UPDATE MASTER AS T1
INNER JOIN `DATE-TYPE_07042016` AS T2 ON T1.recordID = T2.recordID
SET T1.DATE = T2.DATE, T1.NEW_USED = T2.NEW_USED_CODE
WHERE T1.NEW_USED IS NULL
AND T1.RecordID BETWEEN 0 and 1000000
1
 

Author Comment

by:FirstDirect
ID: 41711380
Is there a way to have it work in a LOOP? So that it processes the first million then skips to the next, the next until done?
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 41711388
There really aren't efficient loops in SQL. Why do you want a loop, when you can have it all done in one swoop? If you are worried about server activity, you can add LOW PRIORITY, if you want.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 24

Expert Comment

by:mankowitz
ID: 41711391
Or, if you really want to divide the job into batches, you could use WHERE or partitions.
0
 

Author Comment

by:FirstDirect
ID: 41711450
Hmm, not really what I was looking for.
0
 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
ID: 41711459
I suppose another option would be to make a stored procedure, but it adds unnecessary complexity.

CREATE PROCEDURE `update_all` ()
BEGIN

 DECLARE x  INT;
 
 SET x = 1;
 
 WHILE x  <= 5000000 DO
 UPDATE MASTER AS T1
INNER JOIN `DATE-TYPE_07042016` AS T2 ON T1.recordID = T2.recordID
SET T1.DATE = T2.DATE, T1.NEW_USED = T2.NEW_USED_CODE
WHERE T1.NEW_USED IS NULL and T1.recordID < x; 
 SET  x = x + 1000000; 
 END WHILE;
 
END

Open in new window

1
 

Author Comment

by:FirstDirect
ID: 41716308
Mankowitz, I think you might be on to something. The other option since we have zip codes on the file. Would be to perhaps have it search and update records in a given zip code using a separate ZIP table as a reference table.

I have an index on ZIP5 (5 Digit Zip Code)
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 41717657
Again, this is an option, but still not the best way to do it.

CREATE PROCEDURE `update_all` ()
BEGIN

 DECLARE x  INT;
 
 SET x = 1;
 
 WHILE x  <= 99999 DO
 UPDATE MASTER AS T1
INNER JOIN `DATE-TYPE_07042016` AS T2 ON T1.recordID = T2.recordID
SET T1.DATE = T2.DATE, T1.NEW_USED = T2.NEW_USED_CODE
WHERE T1.NEW_USED IS NULL and T1.zip5 = x; 
 SET  x = x + 1; 
 END WHILE;
 
END

Open in new window

0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
physical_device_name field in SQL 3 30
mysql db 3 69
mysql query for sum() 3 28
Software for Local History Museum Library 5 43
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question