Slick 3.0批量插入或更新(upsert) [英] Slick 3.0 bulk insert or update (upsert)
本文介绍了Slick 3.0批量插入或更新(upsert)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在Slick 3.0中执行批量insertOrUpdate的正确方法是什么?
what is the correct way to do a bulk insertOrUpdate in Slick 3.0?
我正在使用适合查询的MySQL
I am using MySQL where the appropriate query would be
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
这是我当前的代码,它非常慢:-(
Here is my current code which is very slow :-(
// FIXME -- this is slow but will stop repeats, an insertOrUpdate
// functions for a list would be much better
val rowsInserted = rows.map {
row => await(run(TableQuery[FooTable].insertOrUpdate(row)))
}.sum
我正在寻找的等同于
def insertOrUpdate(values: Iterable[U]): DriverAction[MultiInsertResult, NoStream, Effect.Write]
推荐答案
有几种方法可以使此代码更快(每个应该都比前面的代码要快,但是它会逐渐变得更快).习惯用法少一些):
There are several ways that you can make this code faster (each one should be faster than the preceding ones, but it gets progressively less idiomatic-slick):
-
如果在slick-pg 0.16.1+上运行
insertOrUpdateAll
而不是insertOrUpdate
await(run(TableQuery[FooTable].insertOrUpdateAll rows)).sum
一次运行所有DBIO事件,而不是等每个事件都提交之后再运行下一个:
Run your DBIO events all at once, rather than waiting for each one to commit before you run the next:
val toBeInserted = rows.map { row => TableQuery[FooTable].insertOrUpdate(row) }
val inOneGo = DBIO.sequence(toBeInserted)
val dbioFuture = run(inOneGo)
// Optionally, you can add a `.transactionally`
// and / or `.withPinnedSession` here to pin all of these upserts
// to the same transaction / connection
// which *may* get you a little more speed:
// val dbioFuture = run(inOneGo.transactionally)
val rowsInserted = await(dbioFuture).sum
下降到JDBC级别并一次运行所有upsert(通过此答案想法) :
val SQL = """INSERT INTO table (a,b,c) VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);"""
SimpleDBIO[List[Int]] { session =>
val statement = session.connection.prepareStatement(SQL)
rows.map { row =>
statement.setInt(1, row.a)
statement.setInt(2, row.b)
statement.setInt(3, row.c)
statement.addBatch()
}
statement.executeBatch()
}
这篇关于Slick 3.0批量插入或更新(upsert)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文