时间戳

首页 > 解决方案 > If there is a way to pre-calculate SQL View in order to speed-up queries from it?

python - If there is a way to pre-calculate SQL View in order to speed-up queries from it?

问题描述

I perform numerous different SELECTs from an SQLite VIEW (dynamic table) using a cycle in Python. The underlying SQL query for the VIEW takes about 5 seconds to complete. According to my current understanding, the VIEW is recalculated every time, when I perform a SELECT from it. Since within the Python cycle, I do not update any tables, I wonder if there is a way to "freeze" (precalculate) the VIEW as a static table, perform fast SELECTs from it and after "release" it.

Before I tried to optimize the underlying query code of the VIEW and managed to reduce the execution time from 16 to 5 seconds. I tried also to find in Internet commands similar to "BEGIN;" and "COMMIT;" which do a great job for updating the tables, but could not find any.

My query which I perform from the Python loop looks like that:

SELECT date, spread FROM futures_spreads_close 
WHERE commodity_id=? AND exp_month_id=? AND exp_year=? 
ORDER BY date;

where the questions marks are the loop indexes.

If I re-implement all the logic from the VIEW into the Python code, I can have spped-up for about 100 times. However, I would like to keep all the logic inside the SQL. I believe that there is a more efficient way to perform queries on a VIEW.

标签： pythonsqlsqliteselectview

解决方案

如果您使用慢速视图运行多个查询，其行不会在不同查询之间更改，您可以通过将视图的行缓存在临时表中来实现它，并在查询中使用该表：

CREATE TEMP TABLE mat_view AS SELECT * FROM actual_view;
-- Create indexes if needed

关闭数据库连接时会删除临时表，或者您可以DROP在使用它的查询完成后显式删除它。

推荐阅读