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
-
if file have similar structure with table, fasted way will be use load from file - dev.mysql.com/doc/refman/5.7/en/load-data.html. If not - Your way will be faster. – a_vlad Nov 21 '16 at 10:18
-
I am about to create a new table, so I could have used this method, extra columns could have been added later. Drawback: I am doing some cleanup with data, so if I insert directly from file then I will have to read each record again from db, process it, and update the table. I have checked, updating is much slower than insertion. – Kamal Nayan Nov 21 '16 at 10:28
-
1. exactly this I mean , 2. all this cleaning operation You can do in procedure and no single true version of answer - all depend from transformations. Perl script could spend a lot time for row by row transformation, and in some case all of them could be changed for 1 or UPDATES/SELECTS in stored procedure - need understand the process for correct answer. – a_vlad Nov 21 '16 at 10:41
-
Whole process goes as: I have tsv containing first_name, last_name, email, mobile, alt_email, alt_mobile. My cleaning code would be to cleanup mobile numbers and email ids to be a valid one. Also, if there is no email present but alt_email, then value of emails will be swapped and so with mobile. After cleaning, a new column would be updated with some values. And be some more minor changes. – Kamal Nayan Nov 21 '16 at 10:47
-
look all depend from HOW. For example - if You for each row make lookup in database - stored procedure will be faster, just because (even if use cursor and not bulk queries) just because - no time for connect and send data. If You make for example - DNS lookup for email - no choices procedure can not do this. I can provide You example when change perl script for stored procedure decrease time from 11Hr to 45sec ... but this example not true for all case, in this project we can find proper logic, in some cases it not possible. – a_vlad Nov 21 '16 at 10:54
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.
answered Nov 21 '16 at 10:05
-
Its battle of 10000 procedure calls versus 1 insert call. Will it still be faster? Thanks for answering! – Kamal Nayan Nov 21 '16 at 10:13
-
1
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.
answered Nov 21 '16 at 18:50
-
While trying
LOAD DATA, I'm gettingerror 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement. I found its two solution, 1: Upload file inC:\ProgramData\MySQL\MySQL Server 5.7\Uploads, 2: changesecure_file_privinmy.iniand restart. First one didn't work for me. Lemme try for the second one. Any suggestion, why first one didn't work? – Kamal Nayan Nov 23 '16 at 4:57
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:
- 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
- Created table with required columns (without index)
- Used
LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE users FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';Time taken: ~14 min - Added few more required columns that were not in csv file
- Created Index on required columns
Here are the issues that I faced and how it got recovered:
- Initially I've used
LOAD DATA INFILE 'file.csv' into TABLE users FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';that gave me an errorerror 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 inC:\ProgramData\MySQL\MySQL Server 5.7\Uploads, as in global variablesecure_file_priv
B. Deletesecure_file_privfrommy.iniand restart MySQL server.
Unfortunately neither of above worked for me. Finally, read docs and triedLOCALwith 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.
