sqlite - 在带有GROUP BY的UNION上进行内部联接是否应该在SQLite中花费几个小时？

Question

我正在尝试学习SQLite并寻找技术来加快查询速度。当我很容易地处于百万秒级时，我看到这里有人试图挤出ms。我有一个带有四个表的SQLite数据库，尽管我只查询三个表。这是查询（我正在使用R来调用查询）：

SELECT a.date, a.symbol, SUM (a.oi*a.contract_close) AS oi, c.ret, c.prc
    FROM (SELECT date, symbol, oi, contract_close FROM ann
            UNION
            SELECT date, symbol AS sym, oi, contract_close FROM qtr
            WHERE oi > 100 AND contract_close > 0 AND date > 20090600) a
    INNER JOIN
    (SELECT date, symbol || '1C' AS sym, ret, prc FROM crsp
            WHERE prc > 5 AND date>20090600) c
    ON a.date = c.date AND a.symbol = c.sym
    GROUP BY a.date, a.symbol

我在每个表上都按日期和符号都有一个索引，只是进行了VACUUMed处理，但是仍然很慢，例如一个小时以上（请注意，我正在寻找六个月的子集...我真的想查询回去2003）。

这仅仅是缓存大小的问题吗？我有一台相对较新的笔记本电脑（配备4GB RAM的MacBook Pro）。谢谢！

这是 .schema：

CREATE TABLE ann 
( "date" INTEGER,
 symbol TEXT,
 contract_type_1 TEXT,
 contract_type_2 TEXT,
 product_type TEXT,
 block_volume INTEGER,
 oi_change INTEGER,
 oi INTEGER,
 efp_volume INTEGER,
 total_volume INTEGER,
 name TEXT,
 contract_change INTEGER,
 contract_open INTEGER,
 contract_high INTEGER,
 contract_low INTEGER,
 contract_close INTEGER,
 contract_settle INTEGER 
);
CREATE TABLE crsp 
( "date" INTEGER,
 symbol TEXT,
 permno INTEGER,
 prc REAL,
 ret REAL,
 vwretd REAL,
 ewretd REAL,
 sprtrn REAL 
);
CREATE TABLE dly 
( "date" INTEGER,
 symbol TEXT,
 expiration INTEGER,
 product_type TEXT,
 shares_per_contract INTEGER,
 "open" REAL,
 high REAL,
 low REAL,
 "last" REAL,
 settle REAL,
 change REAL,
 total_volume INTEGER,
 efp_volume INTEGER,
 block_volume INTEGER,
 oi INTEGER 
);
CREATE TABLE qtr 
( "date" INTEGER,
 symbol TEXT,
 total_volume INTEGER,
 block_volume INTEGER,
 efp_volume INTEGER,
 contract_high INTEGER,
 contract_low INTEGER,
 contract_open INTEGER,
 contract_close INTEGER,
 contract_settle INTEGER,
 oi INTEGER,
 oi_change INTEGER,
 shares_per_contract INTEGER,
 expiration INTEGER,
 product_type TEXT,
 unk TEXT,
 name TEXT 
);
CREATE INDEX idx_ann_date_sym ON ann (date, symbol);
CREATE INDEX idx_crsp_date_sym ON ann (date, symbol);
CREATE INDEX idx_dly_date_sym ON ann (date, symbol);
CREATE INDEX idx_qtr_date_sym ON ann (date, symbol);

Answer 1

您没有提到关键的信息，即每个表中有多少行以及结果集中有多少行。除非您拥有大量数据集，否则查询不应该花费一个小时。

就是说，我注意到有关您的查询的几件事：


我假设您知道在您的UNION中，WHERE子句仅适用于第二个表，并且您将包含整个“ ann”表？
通常，除非确实需要普通UNION提供的重复数据删除功能，否则UNION ALL通常比普通UNION更快。
您无需在JOIN两侧的日期字段上重复过滤器。一侧就足够了，根据放置滤镜的JOIN的哪一侧，您可能会获得不同的速度结果。通过在两个地方都使用它，您可能会欺骗查询优化器。
我不确定在UNION的第二个SELECT中做什么“ AS sym”，因为该列在输出中（从UNION的第一个SELECT中被命名为“ symbol”），而您依赖的是名称符号在您的主SELECT语句中。
在您的主要SELECT语句中，聚合函数中没有c.ret和c.prc，但是您没有将它们包含在GROUP BY中，因此，我不清楚您希望在结果中看到什么值c包含GROUP BY集的多个行的事件。
无法优化JOIN，因为您正在计算JOIN值之一作为内部SELECT的一部分。我不确定是否有一种聪明的方法可以重写JOIN条件，使其在不存储计算的符号值的情况下可被优化。
根据符号和日期值的分布，您可能希望反转索引中列的顺序（但仅当您解决了计算符号值的问题时）。