为什么sqlite3应用程序立即回答这个查询，但使用sqlitejdbc却很慢？

发布于 2024-08-21 05:50:37 字数 910 浏览 3 评论 0原文

我使用的是 Mac OS X v10.4.11 及其附带的标准 Java (1.5.0_19) 和 sqlite3 (3.1.3)。（是的，有点旧......但请参阅下面的评论。）

我有一个 sqlite3 数据库，其中有一个包含数十万行的表，其中包含“名称”和“存储”文本列。名称是六个（到目前为止）短字符串之一；存储的是 19 个字符的标准日期时间字符串。每列都有一个独立的索引。只有六个唯一名称值。以下查询：

select distinct name from myTable where stored >= date("now");

当我通过 Mac OS X“sqlite3”应用程序执行它时，立即列出相关名称。但是，当我在应用程序中以通常的方式执行相同的操作时，需要超过 2 秒的时间才能找到每个名称（总共大约 15 秒）：

String         q = "SELECT DISTINCT name FROM myTable " +
                    "WHERE stored >= DATE('now');" ;
try {
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(q);
    while (rs.next()) {
        final String s = rs.getString("symbol");
        System.err.println("Got " + s);
    }
    rs.close();
}

我已经使用 sqlitejdbc-v054 和 sqlitejdbc-v055 尝试过此操作。没有明显的差异。

这是一个已知的缺陷吗？如果没有，有人有什么建议如何攻击它吗？

原文

I'm using Mac OS X v10.4.11 with the standard Java (1.5.0_19) and sqlite3 (3.1.3) that came it. (Yeah, a little old... but see comment below.)

I have a sqlite3 database with a table with a few hundred thousand rows, with "name" and "stored" text columns. Name is one of six (so far) short strings; stored is a 19-character standard date-time string. Each column has a stand-alone index. There are only six unique name values. The following query:

select distinct name from myTable where stored >= date("now");

lists the relevant names instantly when I perform it through the Mac OS X "sqlite3" application. But it takes over 2 seconds to find each name (total of about 15 seconds) when I do the same thing in the usual way in my application:

String         q = "SELECT DISTINCT name FROM myTable " +
                    "WHERE stored >= DATE('now');" ;
try {
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(q);
    while (rs.next()) {
        final String s = rs.getString("symbol");
        System.err.println("Got " + s);
    }
    rs.close();
}

I've tried this with both sqlitejdbc-v054 and sqlitejdbc-v055 . No perceptible difference.

Is this a known deficiency? If not, anyone have any suggestions how to attack it ?

分享到QQ

分享到微博