On Mon, Jul 4, 2016 at 10:13 PM, Andrey Stepnov <[email protected]> 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 >>> >
