时间戳

首页 > 解决方案 > SQLite 闭包表实现的性能问题

performance - SQLite 闭包表实现的性能问题

问题描述

我首先要说我对 SQL 和数据库系统相当陌生，所以请原谅我可能会犯的任何新手错误。

我正在使用闭包表在 SQLite 数据库中插入分层数据。我正在为 SQLite 版本 3.26.0 使用 C# (.NET 4.6.1) 和 SQLite 预编译的 32 位 DLL (x86)。插入的分层数据包含约 240000 个元素，最大树深度不大于 7。

我的分层元素表是：

CREATE TABLE element (elementId INTEGER PRIMARY KEY, parentId INTEGER, elementName TEXT, FOREIGN KEY (parentId) REFERENCES element(elementId));

我的闭包表定义为：

CREATE TABLE hierarchy (parentId INTEGER, childId INTEGER, depth INTEGER, FOREIGN KEY(parentId) REFERENCES element(elementId), FOREIGN KEY(childId) REFERENCES element(elementId));

元素使用经典堆栈插入，该堆栈从“根”元素开始，在事务处理期间将元素的成员添加到其中，使用：

INSERT INTO element VALUES (<ELEMENT_ID>, <PARENT_ID>'<ELEMENT_NAME');

我用“自我”关系初始化闭包表，使用：

INSERT INTO hierarchy(parentId, childId, depth) VALUES (<ELEMENT_ID>, <ELEMENT_ID>, 0);

这些插入不会导致任何问题，只需几秒钟即可执行。

接下来，我使用相同的堆栈方法再次遍历所有元素以构建闭包表（注意：我可能会在前面的指令的同时执行此操作，但是我在单独的循环中执行此操作以隔离性能问题）使用以下代码（在另一个事务中）：

INSERT INTO hierarchy SELECT p.parentId, c.childId, p.depth+c.depth+1 FROM hierarchy p, hierarchy c WHERE p.childId=<PARENT_ID> AND c.parentId=<ELEMENT_ID>;

但是，此查询需要数小时甚至数天才能执行。它的执行时间也越来越长。我知道它在闭包表中插入了很多元素（当前元素与其所有祖先之间的每个关系一个条目），但我想知道是否可以采取任何措施来提高这里的性能？

谢谢

标签： performancesqlitehierarchical-data

解决方案

您需要子键和父键的索引。还将所有内容包装在事务中。

更好的是，使用单个递归 CTE 生成闭包表，例如

with recursive
closure as (
    select elementId, elementId as parentId, 0 as depth from element
    union all
    select closure.elementId, element.parentId, 1 + depth as depth
    from closure, element where closure.parentId = element.elementId
)
select * from closure

要实际创建表，请使用类似create table hierarchy as将上述结果放入表中的方法。

推荐阅读