While I am not a user of MariaDB/MySQL, this may be of help.
The most common way to speed bulk record insertion avoids your current 1
record per insertion loop by inserting multiple values [rows] with each
INSERT. In SQL:
INSERT INTO MyTable
(id, firstname, lastname)
VALUES
(1, "Jane", "Smythe") ,
(2, "John", "Jones") ,
(3, "April", "Marlson") ;
Using a loop that increments in larger steps (n at once) and getting the
corresponding n records into a single INSERTion should improve things.
This sort of loop behaves in the following way (if you had 10 or 11
records in this example, the final few would be inserted individually at
the end).
for i in 1:3:9
println( (i, i+1, i+2) )
end
(1,2,3)
(4,5,6)
(7,8,9)
On Sunday, July 3, 2016 at 1:54:03 PM UTC-4, Andrey Stepnov wrote:
>
> Hi Community!
>
> I just try to INSERT around 580K rows to MaraDB-table from array B:
>
> julia> B
> 586973x4 Array{Float64,2}:
> 54.1958 142.929 0.15 333.636
> …
>
>
> using MySQL
> con = mysql_connect("127.0.0.1", "DB", "PASS", "srss_api_production")
>
> # convert to tuple for big insert
> pgavals =
> Array{Tuple{Float64,Float64,Float64,Float64}}(0)
>
>
> for i in 1:length(B[:,1])
> pgavals = push!(pgavals, (B[i,1], B[i,2], B[i,3], B[i,4]))
> end
>
> julia> pgavals
> 586973-element Array{Tuple{Float64,Float64,Float64,Float64},1}:
> (54.195835,142.929169,0.15,333.63588213260454)
> ...
>
>
> # prepare
> mysql_stmt_prepare(
> con,
> """
> INSERT INTO srss_api_production.pga_data
> (report_id, version_id, lat, lon, pga_value,
> ep_dis)
> values
> (10, 2, ?, ?, ?, ?);
> """
> )
> # insert
> for i in pgavals
> mysql_execute(
> con,
> [MYSQL_TYPE_FLOAT,
> MYSQL_TYPE_FLOAT,
> MYSQL_TYPE_FLOAT,
> MYSQL_TYPE_FLOAT],
> i
> )
> end
>
>
> AND it extremely slow! I think because it generates 1 INSERT request for
> each row…
>
> Please help me find out simple (or probably not) way to make INSERT
> faster.
>
> Andrey
>
>
