Company has a labor personnel information table data, with 3500 w started inside requirements are in accordance with the administrative divisions inserted the data in the database to another,
Cursor is administrative divisions, a total of 40 a few areas, the overall execution of the stored procedure to four hours,
Test today, however, singled out a 1000 w data area of the implementation process, the efficiency is as high as 7 minutes,
Not very understand, because each region after batch insert will submit, should not be such a big difference of efficiency,

CodePudding user response:

How long will it take to get you first test under local insert, because of who you are, but because of cross-database plus network transmission time

CodePudding user response:

reference 1st floor sxq129601 response:

how long will it take to get you first test under local insert, because you are across the library along with the network transmission time

Is in the local test first

CodePudding user response:

Database cache, behind the first execution and performance of several times has obvious difference is a more common,
Don't you try all data operation, by a further 4 hours?
Database performance test, to test for many times, eliminate difference greatly as a result,

CodePudding user response:

How do you write a stored procedure? Just stick to the code,

CodePudding user response:

1000 w common hardware equipment should be match for 7 minutes?

The partition table? APPEND? NOLOGGING? Partition exchange?

CodePudding user response:

Remove the index a try,

CodePudding user response:

Create DBLink, then insert into the new table select * from the original table @ dblinkname