Which will be faster for Millions of records? Procedure or Insert statement with multiple values?

Question

I have written a perl script that reads row from a tab separated txt file, do some processing on it, and inserts into the MySQL table.

Currently, I am making an insert query with 10,000 records (from the script) and finally execute the statement.
insert into tablename values (1, 'kamal'), (2, 'nayan'), ......, (10000, 'kumar');

Another approach that came in my mind is stored procedure. Can anyone tell me how can I utilise stored procedure to make my queries faster? Right now, I have tested stored procedure to insert one row at a time, but that would need 10000 procedure calls, while insert statement is asking for just one call.

Any help would be appreciated.
Engine: InnoDB
Version: 5.5.45

Answer 1

Stored Procedure is better, why? Read this: http://www.sqlbook.com/SQL-Server/Why-use-Stored-Procedures-41.aspx

When a query runs, it calculates the most efficient method of processing the query and stores it in something called the execution plan. Depending on the query, a good deal of the time taken to execute a query is actually spent on calculating the execution plan.

When inline SQL statements are executed the execution plan must be generated each time the query runs. When a stored procedure is called its execution plan is stored in memory and the next time the stored procedure is called the execution plan is retrieved from memory without needing to be recalculated. This increases the speed of execution and improves database performance.

Answer 2

LOAD DATA is usually the fastest way to load lots of rows.

A single INSERT with 10K rows is a close second. If you have too many rows, you could hit some limit somewhere, and fail.

Then comes a bunch of INSERTs, each with 100 rows.

Farther down the list is 10K 1-row INSERT statements.

Probably below that is calling a stored procedure 10K times. It is unclear whether the extra overhead for the CALL is less or more than any savings in "compiling" the SP.

If the client and server are separated by a long distance (WAN), then the dominant timing issue is the latency between them. So minimizing the number of roundtrips between them overrides all of what I have said so far.

Answer 3

Thanks everyone for comments / answers. I cross checked my tsv file, few rows were malformed, so I couldn't use LOAD DATA directly. Here is what I followed to do it faster:

1. Written a perl script to read from tsv file, did cleaning and other processing, finally written to a csv. Time taken: ~4 min for 30 Million records
2. Created table with required columns (without index)
3. Used LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE users FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; Time taken: ~14 min
4. Added few more required columns that were not in csv file
5. Created Index on required columns

Here are the issues that I faced and how it got recovered:

1. Initially I've used LOAD DATA INFILE 'file.csv' into TABLE users FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; that gave me an error error 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement.
   
   Googled and found its two solution:
   A. Upload file in C:\ProgramData\MySQL\MySQL Server 5.7\Uploads, as in global variable secure_file_priv
   B. Delete secure_file_priv from my.ini and restart MySQL server.
   
   Unfortunately neither of above worked for me. Finally, read docs and tried LOCAL with the query as I have given in step 3, And wow it worked.
   
   Hope this answer will help someone who is also struggling in such scenarios.