Everyone a great god, and now a difficulty encountered in the development process, a detailed description is as follows: there are two tables a temporary table only four fields, the table data is 30 million, and a current table data quantity is 200000, is to address the contents of the temporary table to update the current the data in the table, my train of thought is to use the current table and temporary table joint query out all in accordance with the current table data, then according to query the data to update the current list of the three fields, query efficiency has no problem but now with joint query out 200000 data update current table is to use ordinary update statement to update the execution efficiency of slow an hour less than hundreds of data update, please everyone a great god is there a way to help the current table can update can't delete reconstruction, thank you

CodePudding user response:

Update an hour less than 100000? And only four fields is your connection to write good complex

CodePudding user response:

200000 data update current table is to use ordinary update statement to update the execution efficiency of slow an hour less than thousand data updated

A an update?

Posted a statement;

CodePudding user response:

Use the merge.. Into, do not use the update, I also work recently appeared similar problem, update the data in the 6 million to four hours, with the merge finally.. 10 minutes into it, hope to adopt,

CodePudding user response:

With the upstairs, with the merge
But using the merge, also want to stick your statement and execute the plan, because the execution plan may also be pit

CodePudding user response:

Suggestions:
1. Update if optimization is not good, can consider to delete the insert later, usually much faster
2. The merge into usable, but be careful, write bad error prone, resulting in abnormal data
3. Please help to provide specific SQL table structure and analysis program, said that it is difficult to determine the problem alone

CodePudding user response:

The SQL issued you come with me first, and then said in the table below, the SQL optimization points minutes to help you