Slick 3 - upsert 工作太慢 [英] Slick 3 - upsert works too slow
问题描述
我对 upsert
列表 items
case class Item(id: String, text: String)
class Items(tag: Tag) extends Table[Item](tag, "items"){
...
}
val tbl = TableQuery[Items]
def insertItems(items: List[Item]):Future[Int] = {
val q = DBIO.sequence(items.map(tbl.insertOrUpdate).toSeq).map(_.sum)
db.run(q)
}
对于长度为 2000 的 items
列表,upsert
需要大约 10 秒.太长了...
For items
list with length 2000, upsert
takes ~10 seconds. It's too long...
我认为,大部分时间都花在编译查询上.
I think, most part time takes compiling queries.
我应该如何重写insertItems
来加速它?
How should I rewrite insertItems
for acceleration it?
推荐答案
Use compiled queries
( docs).AFAIK,插入的编译查询在 slick 2.0 之后可用.
Use compiled queries
( docs ). AFAIK, Compiled queries for insert are available after slick 2.0.
另外,要插入一个列表,你应该做一个批量操作,而不是一个一个地插入一个记录.
Also, to insert a list, you should do a batch operation rather than inserting a record one by one.
所以,在 Slick-3.0 中,对于插入,你应该这样做:
So, in Slick-3.0, for insert, you should do:
val tblCompiled = Compiled(TableQuery[Items])
tblCompiled ++= items
然后运行另一个查询以获取所需列的总和.
Then run another query to get the sum of the desired column.
我不认为,slick 支持批量 insertOrUpdate 语句.如果底层数据库支持批量 insertOrUpdate,最快的方法是编写纯 SQL.否则编译的 insertOrUpdate
查询应该相当快.
I don't think, slick supports bulk insertOrUpdate statements. If the underlying db supports bulk insertOrUpdate, the fastest approach will be to write plain SQL. Otherwise compiled insertOrUpdate
query should be decently fast.
代码应该类似于
items.map(tblCompiled.insertOrUpdate)
这篇关于Slick 3 - upsert 工作太慢的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!