Hi Palli!
Thanks for reply.
Here is code with transactions example:
mysql_query(con, "SET autocommit = 0")
mysql_query(con, "START TRANSACTION;")
mysql_stmt_prepare(
con,
"""
INSERT INTO srss_api_production.pga_data
(report_id, version_id, lat, lon, pga_value,
ep_dis)
values
(?, ?, ?, ?, ?, ?);
"""
)
@time for i in pgavals
mysql_execute(
con,
[MYSQL_TYPE_FLOAT,
MYSQL_TYPE_FLOAT,
MYSQL_TYPE_FLOAT,
MYSQL_TYPE_FLOAT,
MYSQL_TYPE_FLOAT,
MYSQL_TYPE_FLOAT],
i
)
end
mysql_query(con, "COMMIT;")
mysql_query(con, "SET autocommit = 1")
> Then do your INSERTs (prepared statments can be better here for security
but not speed, while can also help for speed in queries), e.g in a loop.
Yes, you right! It works with smal data but if I have:
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)
it's extreamly slow.
Solution: string with non-safe big-insert:
Andrey
On Wednesday, July 6, 2016 at 1:11:42 AM UTC+11, Páll Haraldsson wrote:
>
> On Tuesday, July 5, 2016 at 7:07:09 AM UTC, Andrey Stepnov wrote:
>>
>> Hi Ivar!
>>
>> There is no documentation about transactions for MySQL.jl. I understand
>> that it wrap C libs but it's not clear how to call transaction mechanism
>> from julia.
>>
>
> I do not know for sure about MySQL, as I'm a PostgreSQL guy, but it (as
> most databases, but not the standard?) starts in autocommit mode.
>
> To start a transaction you do (these commands as any "other" query, not
> like INSERT is a query..):
>
> BEGIN; -- OR BEGIN WORK; -- that may be in the standard way..
>
> Then do your INSERTs (prepared statments can be better here for security
> but not speed, while can also help for speed in queries), e.g in a loop.
> This way you do not need individual multi-inserts.
>
> COMMIT; -- OR COMMIT WORK; -- here ROLLBACK is an alternative (I guess
> with InnoDB, but not traditional MySQL).
>
>
> This is independent of how you execute queries, but some database
> abstraction layers have something like beginTransation() etc. [This might
> be available in a database abstraction layer, those are a good idea anyway,
> as you do not want to be tied to MySQL.]
>
> That isn't really needed; It doesn't do anything more complicated, unless
> you have nested transactions, that you probably do not need. You may want
> to loop up "mini-batching" also if you have a lot of inserts (pros and
> cons), that is if the database does not handle number of inserts.
>
> I will be glad if you can provide very short explanation or example.
>
> --
> Palli.
>
