fast way to load 16,000 rows of data in mysql db

Question

currently i have 16k rows of data in an array, i am using a for loop to loop through the data and excute an INSERT statement. In other words, each loop executes an "INSERT" statement in mysql. This is done 16000 times. This take a long time.

is there a better way to inset bulk data?.

Without any code I can only guess. You could try storing all your values in an array and then just doing one insert. — noel, Oct 10 '12 at 02:29
What is your table type, are you using autocommit after every insert statement? — Adrian Cornish, Oct 10 '12 at 02:46

score 0 · Accepted Answer · edited May 23 '17 at 11:48

0

You can try this as per post by Pascal MARTIN:

insert into your_table (field1, field2, field3)
values 
  (value1_1, value1_2, value1_3), 
  (value2_1, value2_2, value2_3), 
  (value3_1, value3_2, value3_3)

edited May 23 '17 at 11:48

Community

1
1

answered Oct 10 '12 at 02:29

Oh Chin Boon

23,028
51
143
215

kazinix · Answer 2 · 2012-10-10T02:47:35.760

I accomplished this in SQL using this code (with sqlParameters):

    Dim Command As New SqlCommand(_
            "insert into hilmarc_cem_items " & _
            "(CEMID, " & _
            "ItemCode, " & _
            "UnitPrice, " & _
            "Quantity, " & _
            "UOM) "     


    Dim ItemCodes() As String = Request.Form.GetValues("ItemCode")
    Dim UnitPrices() As String = Request.Form.GetValues("UnitPrice")
    Dim Quantities() As String = Request.Form.GetValues("Quantity")
    Dim UOMs() As String = Request.Form.GetValues("UOM")

    For Counter = 0 To ItemCodes.Length - 1
        Command.CommandText &= "select @CEMID, @ItemCode" & Counter & ", @UnitPrice" & Counter & ", @Quantity" & Counter & ", @UOM" & Counter & " "
        Command.Parameters.Add("@ItemCode" & Counter, Data.SqlDbType.NVarChar).Value = ItemCodes(Counter)
        Command.Parameters.Add("@Quantity" & Counter, Data.SqlDbType.Decimal).Value = Quantities(Counter)
        Command.Parameters.Add("@UOM" & Counter, Data.SqlDbType.NVarChar).Value = UOMs(Counter)
        Command.Parameters.Add("@UnitPrice" & Counter, Data.SqlDbType.Decimal).Value = UnitPrices(Counter)
        If Not Counter = ItemCodes.Length - 1 Then
            Command.CommandText &= "union all "
        Else
            Command.CommandText &= ";"
        End If
    Next

The idea is to have a single query, get all data from array and add them in sqlCommand as parameters. The query would be like this:

    insert into myTable 
        (CEMID, 
        ItemCode,
        UnitPrice,
        Quantity, 
        UOM) 
    select @CEMID, @ItemCode0, @UnitPrice0, @Quantity0, @UOM0
    union all
    select @CEMID, @ItemCode1, @UnitPrice1, @Quantity1, @UOM1
    union all
    select @CEMID, @ItemCode1, @UnitPrice1, @Quantity1, @UOM1
    union all

I'm not sure if this is valid in MySQL, just convert to code to MySQL.

score 0 · Answer 3 · answered Oct 10 '12 at 05:22

Try to generate bulk-INSERTs statements. The length of the statement should be less then max_allowed_packet which is the maximum size of one packet or any generated/intermediate string.

If it is possible - temporarily disable or remove indexes in the table.

fast way to load 16,000 rows of data in mysql db

3 Answers3