时间戳

首页 > 解决方案 > 快速设置排序顺序列

sql - 快速设置排序顺序列

问题描述

假设我有一个带有列的 SQLite 表 People recordId TEXT, name TEXT, job TEXT, sortOrder NUM。

我想sortOrder根据名称和作业对表进行排序，为表中的所有行设置列。

我目前正在这样做：

(1)SELECT recordId from People order by name, job

(1b) 在该sqlite3_step()SELECT 命令的循环中，将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) 的 1 秒。

但我试图弄清楚如何使步骤（2）更快。

我确实在recordId列上有一个索引，我认为这将有助于找到每一行以设置sortOrder步骤 (2) 中的值。

如果我使用rowid而不是recordId，它会将步骤 (2) 缩短到 10 秒。

我在想是所有单独的调用sqlite3_exec()都在减慢速度。因此，我尝试通过构建一个巨大的CASE语句在单个 exec 语句中完成所有操作：

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 毫秒内完成。我有这个有序记录 ID 列表，只想说“根据该顺序设置数字列的值”。

也许用recordId和sortOrder列创建一个临时表会更快，然后基于与该表的连接进行更新？

或者也许有一种方法可以在一个步骤/循环中而不是 2 中完成这一切？

（顺便说一句，我意识到，当问题出现时，我可以sortOrder通过在 name 和 job 字段上创建索引来避免对列的需要。但在我的实际应用程序中，我排序的一些字段是计算值, 在某些情况下基于相关表中的值。这就是为什么我首先要有一sortOrder列。也许有一种方法可以根据其他表中的相关值进行索引。但是现在请考虑我的问题.)

标签： sqlsqlitesql-updatesql-order-byquery-optimization

解决方案

为什么不使用 SQLite 3.25 开始版本中提供的窗口函数？

如果我正确地关注了您，以下查询将为您提供所需的结果：

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

您可以只使用此查询来创建视图而不是存储值......当涉及到更新表时，它有点复杂，因为 sqlite 不支持更新语句中的连接。您可以首先实现一个包含排序顺序的临时表，然后使用它来更新您的表，例如：

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
);

推荐阅读