SQLite触发器优化 [英] SQLite trigger optimization

问题描述

这是基于有关查询优化的问题的后续行动。

根据建议，为了进行快速选择，我尝试使用触发器在插入时预先计算一些数据。

In order to make fast selection, as suggested, I tried to pre-compute some data at insertion time using a trigger.

基本上，我想将给定列值的出现次数保存到给定表中。

Basically, I want to keep the number of occurrences of a given column's value into a given table.

以下模式用于存储每个值的出现次数：

The following schema is used to store the occurrences for each of the values:

CREATE TABLE valuecount (value text, count int)
CREATE INDEX countidx ON t (count DESC)
CREATE UNIQUE INDEX valueidx ON valuecount (value);

这是我一直在测试的触发器：

And here is the trigger I've been testing with:

CREATE TRIGGER counttrigger INSERT ON collection
  FOR EACH ROW
    BEGIN
      INSERT OR REPLACE INTO valuecount VALUES 
      (NEW.value, coalesce((SELECT count + 1 FROM count WHERE valuecount.query LIKE 
       NEW.value), 1));
    END;

collection 表中的典型插入包含在单个 INSERT INTO ... SELECT ... 语句中存储数十万行（我正在将数据库中的数据合并到另一个数据库中）。

A typical insertion on the collection table contains a few hundreds thousands of rows, in a single INSERT INTO ... SELECT ... statement (I'm merging data from a db to another).

问题是，使用此触发器，插入300K行需要4个小时，而不是300 ms ...

The problem is that with this trigger, inserting 300K rows takes 4 hours, instead of 300 ms ...

一种在不牺牲性能的情况下实现这一目标的方法？

Is there a way to achieve this without sacrificing performances?

在此先感谢您的帮助，

推荐答案

好，所以看来使用触发器毕竟不是一个好主意。

Ok so it seemed that using a trigger wasn't a good idea afterall.

由于我要合并来自不同数据库的表，所以我做了在插入之前，根据我要合并的表的内容，全部合并到 valuecount 表中。

Since I'm merging a table from different databases, I did the upsert into the valuecount table based on the content of the table I'm merging, all in one statement, before the insert.

因此，我没有一个触发器，而是在我的 INSERT INTO ... SELECT ... 语句之前执行了如下查询： / p>

So instead of a trigger, I just have a query that looks like this, executed prior to my INSERT INTO ... SELECT ... statement:

INSERT OR REPLACE INTO valuecount
SELECT value, coalesce((SELECT count FROM valuecount WHERE valuecount.value = collection.value) + COUNT(value), COUNT(value))
FROM attached.collection GROUP BY value

这篇关于SQLite触发器优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！