sql - 来自外部驱动器的非常慢的简单 SQLite 查询

Question

我在外部硬盘驱动器上有一个 SQLite 数据库，我想用 R(或 Python)分析它的几千个子集。数据是对 13 个变量的大约 1.1 亿次观察，它存储在(~10.5 GB)外部驱动器上，因为我的计算机上没有足够的存储空间。我选择尝试使用 SQL 数据库，因为虽然我没有足够的 RAM 来处理 R 中的整个数据集，但我只需要在任何给定时间访问其中的一小部分。

我用 RSQLite 构建了数据库——本质上是通过使用 dbWriteTable() 附加一堆数据帧——并且可以成功地查询它。问题是，即使是非常简单的数据库查询也需要几分钟才能完成。例如，这将需要大约 3 分钟的时间来运行(在 R 中或在 Python 中使用 sqlite3):

# example in R
library(RSQLite)

# connect to existing database
conn <- dbConnect(RSQLite::SQLite(), EEGdata.db)

# example of simple query
testQuery <- dbSendQuery(conn, "SELECT * FROM Data WHERE Time = 42")

# return data (takes "forever" but does work)
testdf <- dbFetch(testQuery)

我尝试了多种不同的(但我认为等效的)方法来执行查询，例如使用 dbplyr，但它们都同样需要几分钟才能运行。

我试过使用事务(但我认为这主要只是有助于插入或修改信息？)。

我试过使用索引，它确实加快了查询速度，但它们仍然需要超过一分钟才能完成。

我已尝试使用最新的 R/RSQLite 版本，如以下建议: SQLilte query much slower through RSqlite than sqlite3 command-line interface

我测试过我的外部驱动器(WD 我的护照)的一般读/写速度并没有慢到可怕(它以大约 100Mbs 的速度传输数据)。

我也尝试过通过 Python 使用 sqlite3，并得到大致相同的非常慢的查询。

我想我可能以某种方式错误地创建了数据库，但令人费解的是为什么它仍然可以运行，只是非常糟糕。为了以防万一，这是我如何创建数据库的示例:

library(RSQLite)

# open connection to new database
conn <- dbConnect(RSQLite::SQLite(), EEGdata.db)

# loop over data files
for (file in filenames){

  # load file (column names defined elsewhere)
  temp <- read.delim(file = file, sep = '\t', header = F, col.names = columns) 

  # do some cleaning, merging, etc...

  # add to database
  dbWriteTable(conn, "Data", temp, append = TRUE)
}

由于问题在 R 和 Python 中看起来很相似，我假设我创建 SQLite 数据库的方式存在一些问题，或者它存储在外部驱动器上。

关于可能出现的问题或我可以尝试什么的任何想法？

编辑:

感谢所有的想法!

关于索引，正如@Waldi 和@r2evans 所提到的，它确实有帮助——我并不是要暗示它没有用，或者我不会使用索引。对于我将要进行的最复杂的查询(仍然仅按 3 个变量进行过滤)，完全索引将时间从约 6 分钟缩短到约 1.5 分钟。很明显，这些索引值得使用，但它本身似乎并不能解释速度缓慢的原因。

关于较小版本的数据库 (@DavidP)，好主意 - 我只是尝试了一些东西，发现在这种情况下，数据库的实际大小对速度影响不大，但大小从查询返回的数据(特别是行数)。但即使在较小的情况下，它也感觉很慢——例如仅返回 900 行，所有内容都已完全索引，需要 1.3 秒。据我所知，对于简单的 SQL 查询来说，这仍然很慢。

关于 HD 的速度(@Waldi 和@dnoeth)——我想我不想相信这只是一个硬件问题，但这绝对有可能。我会看看是否可以在某个地方找到一个 SSD 来测试它并报告回来。

有关@Parfait(和其他人)的更多信息:'''

sqlite> .schema Data
CREATE TABLE `Data` (
  `Item` INTEGER,
  `Amplitude` REAL,
  `Electrode` INTEGER,
  `Time` INTEGER,
  `Subject` TEXT,
  `Hstatus` TEXT,
  `Word` TEXT,
  `Zipf_freq` REAL,
  `OLD` REAL,
  `Concreteness` REAL,
  `Visual_complexity` REAL,
  `Letter_number` REAL,
  `Morpheme_number` REAL
);
CREATE INDEX time_idx ON Data (Time);
CREATE INDEX idx ON Data (Hstatus, Electrode, Time);

'''

关于不使用 SELECT * - 我尝试只选择一个变量而不是所有变量，它并没有真正提高速度。 (并且在这个应用程序中，我已经把不需要的信息删掉了，所以除了索引变量，我需要选择所有的东西)。

Answer 1

(除了外部 HD，这绝对是一个瓶颈)我有一个类似的问题，它归结为 sqlite 数据库本身。您可以切换到 DuckDB 并使用相同的查询代码。对我来说，在具有 1 亿行的稍微大一点的文件上，速度要快 10 倍以上。 https://duckdb.org/docs/api/r