3

I want to insert atleast 500,000 fresh records in one shot. For which I have used while loop inside procedure. My query is working fine but it is taking alot of time to execute. So, I am looking for a solution using which I can make the process of insertion of large volume of data faster. I have gone through many links but I didn't find them resourceful.

Note:

  1. I want to insert fresh data, not the data from existing table.
  2. Syntactically the code provided below is correct and working. So please do not provide any suggestions regarding syntax.

Below is my procedure:

BEGIN
    DECLARE x INT; 
    DECLARE start_s INT; 
    DECLARE end_s INT; 
    SET x = 0; 

    PREPARE stmt FROM
    'insert into primary_packing(job_id, barcode, start_sn, end_sn, bundle_number, client_code, deno, number_sheets, number_pins, status)
values (?,?,?,?,?,?,?,?,?,?)';

    SET @job_id = job_id, @barcode = barcode, @bundle_number = bundle_number, @client_code = client_code, @deno = deno, @number_sheets = number_sheets, @number_pins = number_pins, @status = 1; 
    set @start_sn = start_sn;
    set @end_sn = end_sn;
    WHILE x <= multiply DO
        set @start_s = (start_sn+(diff*x)); 
        set @end_s = ((end_sn-1)+(diff*x)+diff); 

        EXECUTE stmt USING @job_id, @barcode, @start_s, @end_s, @bundle_number, @client_code, @deno, @number_sheets, @number_pins ,@status; 
        SET x = x + 1; 
    END WHILE;

    DEALLOCATE PREPARE stmt;
END
Community
  • 1
  • 1
Amrit Shrestha
  • 1,620
  • 20
  • 25
  • 1
    https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html – juergen d Sep 30 '15 at 10:21
  • Syntactically is a beautiful word. I also look forward to a solution to this query :) – Martin Sep 30 '15 at 10:42
  • if using innodb -> disable autocommit and use transactions. https://dev.mysql.com/doc/refman/5.0/en/commit.html e.g. start trans; loop and insert...; commit trans. – Jon Black Oct 20 '15 at 19:53

1 Answers1

2

Use MYSQL command LOAD DATA INFILE to load your .csv files records to specific table.

For more information and eg. please reffer the following link

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Channaveer Hakari
  • 2,769
  • 3
  • 34
  • 45
  • If I do this then I have to create csv file first through PHP, where I have to loop through and calculate value for each row. I think this method will take more time than previous method... correct me if I am wrong. – Amrit Shrestha Sep 30 '15 at 15:58
  • Are you having your data in text file or how actually you have collected your data and in which format it is. Even if it is in .txt file the above mentioned method will work fine. – Channaveer Hakari Oct 01 '15 at 05:38
  • No, there is no extra file involved here. I calculate data from within mysql itself. If you go through the sql then you can understand it.. I pass start_sn and end_sn values, and the subset values is calculated and inserted by sql. – Amrit Shrestha Oct 02 '15 at 16:43