I need to load 480.000 rows into a remote database. The database can't be accessed locally, it's an Azure WebApp, so i can't load the SQL statements on the server.
I was wondering if there is a way to speed up things, as the process has been really slow, like 4500 rows in 25 minutes, which feels really like I'm doing something wrong.
I'm using
mysql -uxxxx -pxxxx -hremoteapp-on-azure azure_db < .\wp_term_relationships.sql
to load and the sql file is
INSERT INTO `wp_43_term_taxonomy` VALUES ('2', '2', 'link_category', '', '0', '8');
INSERT INTO `wp_43_term_taxonomy` VALUES ('3', '3', 'post_tag', '', '0', '21');
INSERT INTO `wp_43_term_taxonomy` VALUES ('4', '4', 'post_tag', '', '0', '1423');
INSERT INTO `wp_43_term_taxonomy` VALUES ('5', '5', 'post_tag', '', '0', '21');
INSERT INTO `wp_43_term_taxonomy` VALUES ('6', '6', 'post_tag', '', '0', '106');
INSERT INTO `wp_43_term_taxonomy` VALUES ('16', '16', 'post_tag', '', '0', '41');
INSERT INTO `wp_43_term_taxonomy` VALUES ('18', '18', 'post_tag', '', '0', '154');
INSERT INTO `wp_43_term_taxonomy` VALUES ('20', '20', 'post_tag', '', '0', '279');
INSERT INTO `wp_43_term_taxonomy` VALUES ('21', '21', 'post_tag', '', '0', '41');
INSERT INTO `wp_43_term_taxonomy` VALUES ('25', '25', 'post_tag', '', '0', '429');
I've read about optimizing for INSERT, but in this case the query feel really slow. On my local machine the whole insert is taking 20 seconds, or less, not 20 minutes.
Should i somehow use multiple statements to avoid round trips?i could stream the file in PHP and submit it in 1000 INSERT chunks
EDIT - To be clear, I need to be sure i optimized everything I can optimize from my side as I have no control on the server