Hi Yichao Yu! Thank you!
`IOBuffer()` solve: pgavals 586973-element Array{Tuple{Int64,Int64,Float64,Float64,Float64,Float64},1}: (10,2,54.195835,142.929169,0.15,333.63588213260454) (10,2,54.195835,142.9375,0.15,333.6424133198646) ... A = IOBuffer() @time for i in pgavals print(A, i, ',') end 2.524894 seconds (17.02 M allocations: 658.817 MB, 3.67% gc time) ### <-- very fast VALS = takebuf_string(A); VALS = chop(VALS); @time insquery = """ INSERT INTO srss_api_production.pga_data (report_id, version_id, lat, lon, pga_value, ep_dis) values $VALS; """; 0.024216 seconds (7 allocations: 28.561 MB, 4.46% gc time) @time mysql_execute(con, insquery) 307.948797 seconds (7 allocations: 288 bytes) ### <-- slow connection, it's normal 586973 ### <-- N of inserted rows = N of tuple's elements, so ALL FINE! For history: don't foget increase `max_allowed_packet` size. Andrey On Tuesday, July 5, 2016 at 2:11:39 PM UTC+11, Yichao Yu wrote: > > On Mon, Jul 4, 2016 at 10:13 PM, Andrey Stepnov <myj...@gmail.com > <javascript:>> wrote: > > 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," > > This is O(n^2) > > use a `IOBuffer()` and print into it instead. > > > 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 > >>> > > >