INSERT in a remote server is far too slow, how to speed it up?

Question

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

Answer 1

Build INSERT statements will up to 1000 rows each. Execute them in auto_commit=ON mode.

The former cuts down on round trips, plus other overhead in executing statements. The latter avoids sending BEGIN/COMMIT. Waiting for a million rows before committing, would slow things by building a huge "undo" log, then having to flush it.

Answer 2

Since most mysql versions turn on autocommit, use a transaction to have the server sync data to disk only once and not on every row.

Add at the top of your .sql:

BEGIN TRANSACTION;

and append at the end:

COMMIT;

This will have the added advantage that either all will be inserted or none at all. Same for your php script. Or you may add begin/commit every say 50000 rows to commit in batches.