Hi everyone!
I tryed to construct one INSERT request.
The idea:
julia> pgavals #Array{Tuple{Int64,Int64,Float64,Float64,Float64,Float64},1}:
VALS = ""
for i in pgavals
VALS = string(VALS, "$i,")
end
VALS = chop(VALS) # remove last ',' character
insquery = """
INSERT INTO srss_api_production.pga_data
(report_id, version_id, lat, lon, pga_value, ep_dis)
values
$VALS;
"""
mysql_execute(con, insquery)
It works fine with small arrays and tuples.
*BUT *it impossible with ~500K elements in tuple due to very slow string
concatenation operations. Please look at this example:
julia> pgavals
1617840-element Array{Tuple{Int64,Int64,Float64,Float64,Float64,Float64},1}:
(10,2,54.495834,137.504166,0.0,515.0428287161928)
(10,2,54.495834,137.512497,0.0,514.6500363528039)
(10,2,54.495834,137.520828,0.0,514.2575525299238)
...
VALS = ASCIIString("")
@time for i in pgavals
VALS = VALS * "$i,"
end
#### it spens more than 1 hour and even not copleted!!!!
My platform:
MacBookPro11,1 / 2.8 GHz Intel Core i7
$ julia
_
_ _ _(_)_ | A fresh approach to technical computing
(_) | (_) (_) | Documentation: http://docs.julialang.org
_ _ _| |_ __ _ | Type "?help" for help.
| | | | | | |/ _` | |
| | |_| | | | (_| | | Version 0.4.5 (2016-03-18 00:58 UTC)
_/ |\__'_|_|_|\__'_| |
|__/ | x86_64-apple-darwin15.5.0
Is there any other way?
Andrey
On Monday, July 4, 2016 at 9:47:08 AM UTC+11, Jeffrey Sarnoff wrote:
>
> 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
>>
>>
