快速设置排序顺序列

x33g5p2x 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(287)

假设我有一个带有列的sqlite表 recordId TEXT, name TEXT, job TEXT, sortOrder NUM .
我想设定 sortOrder 基于按名称和作业排序表中所有行的列。
我目前的做法是：
(1) SELECT recordId from People order by name, job （1b）在 sqlite3_step() 循环，保存 recordId 将值转换为 vector<string> orderedRecordIds . 当我们完成这个循环时， orderedRecordIds 有 recordId 按所需顺序排列的值。
（2）在一个循环中，做一个 sqlite3_exec() 对于每个 recordId 窗体的

UPDATE People SET sortOrder = <i> WHERE recordId = '<orderedRecordIds[i]>'

这些都可以，但是太慢了。
对于具有200k条记录的数据库，执行步骤（1）大约需要1秒，执行步骤（2）大约需要12秒。
我不担心做第（1）步的时间。
但我正在想办法让第（2）步更快。
我在Map上有索引 recordId 列，我认为这将有助于找到每一行来设置 sortOrder 步骤（2）中的值。
如果我使用 rowid 而不是 recordId ，它将步骤（2）降低到10秒。
我在想这都是单独打给 sqlite3_exec() 让事情变慢了。所以我试着在一个exec语句中构建一个巨大的 CASE 声明：

UPDATE People SET sortOrder = CASE
    WHEN recordId='abc' THEN 0
    WHEN recordId='def' THEN 1
    /* <and so on for 200k rows> */
END

但这是非常缓慢的。
我觉得应该有一个非常快速的方法来完成第（2）步。相比之下，当我使用create index为这个大表的一列创建索引时，它需要大约20毫秒的时间
也许使用 recordId 以及 sortOrder 列，然后基于与该表的联接进行更新？
或者有一种方法可以在一个单步/循环中完成这一切，而不是2步？
（顺便说一句，我意识到当问题出现时，我可以避免 sortOrder 只需在name和job字段上创建一个索引。但在我的实际应用程序中，我所排序的一些字段是计算值，在某些情况下是基于相关表中的值。这就是为什么我想有一个 sortOrder 列在第一位。也许有一种方法可以根据其他表中的相关值编制索引。但现在请考虑我的问题。）

sql sqlite sql-update query-optimization sql-order-by

来源：https://stackoverflow.com/questions/62615711/setting-a-sort-order-column-quickly

1条答案

按热度按时间

mefy6pfw1#

为什么不使用sqlite 3.25版的窗口函数呢？
如果我正确地跟踪了您，下面的查询将为您提供所需的结果：

select p.*, row_number() over(order by name, job) rn from people p;

您可以使用此查询创建一个视图，而不是存储值。。。当涉及到更新表时，它要复杂一些，因为sqlite不支持update语句中的联接。您可以首先具体化包含排序顺序的临时表，然后使用它来更新表，如：

create temp table people_tmp as
select recordId, row_number() over(order by name, job) rn from people;

update people
set sortOrder = (
    select rn from people_tmp pt where pt.recordId = people.recordId
);

赞(0）回复(0）举报 2021-08-09

我来回答

快速设置排序顺序列

1条答案

相关问题

热门标签

最新问答