使用java (netbeans)的Sqlite非常慢

Question

我用sqlite存储数据。我试图从sqlite视图中获取数据，并在java中填充对象数组，但是执行查询需要很长时间。我只有32个对象有22个字段，sqlite有380行。但是，为了执行类似的语句，我花了17秒钟的时间完成了32个对象。

 sql = "SELECT "
                        + "           field1,"
                        + "           field2,"
                        ....
                        + "           field22"
                        + " from Rankedview WHERE Ranking = " + Integer.toString(RankingIndex);
 try (ResultSet rs = stmt.executeQuery(sql)) {
                    while (rs.next()) {
                        a[j].field1= rs.getString("field1");
                        ..........
                        a[j].field22 = rs.getInt("field22");
                    }

                }

在我将sqlite驱动程序从3.7.2升级到3.8.5之后，时间从17秒降到了9秒。

​

我如何提高它的性能？

编辑：

视图定义(ATP是一个表)

CREATE VIEW Ranked AS
SELECT p1.ID,
       p1.field2,
       ...

       p1.field21,
       (
           SELECT count() + (
                                SELECT count() + 1
                                  FROM Table AS p2
                                 WHERE p2.field21 = p1.field21 AND 
                                       p2.id > p1.id
                            )
             FROM ATP AS p2
            WHERE p2.field21 > p1.field21
       )
       AS Ranking
  FROM ATP AS p1
 ORDER BY Ranking ASC;

解释查询计划输出：

selectid order from detail
0   0   0   SCAN TABLE ATP AS p1
0   0   0   EXECUTE CORRELATED SCALAR SUBQUERY 1
1   0   0   SCAN TABLE ATP AS p2
1   0   0   EXECUTE CORRELATED SCALAR SUBQUERY 2
2   0   0   SEARCH TABLE ATP AS p2 USING INTEGER PRIMARY KEY (rowid>?)
0   0   0   EXECUTE CORRELATED SCALAR SUBQUERY 3
3   0   0   SCAN TABLE ATP AS p2
3   0   0   EXECUTE CORRELATED SCALAR SUBQUERY 4
4   0   0   SEARCH TABLE ATP AS p2 USING INTEGER PRIMARY KEY (rowid>?)
0   0   0   EXECUTE CORRELATED SCALAR SUBQUERY 5
5   0   0   SCAN TABLE ATP AS p2
5   0   0   EXECUTE CORRELATED SCALAR SUBQUERY 6
6   0   0   SEARCH TABLE ATP AS p2 USING INTEGER PRIMARY KEY (rowid>?)
0   0   0   USE TEMP B-TREE FOR ORDER BY

Answer 1

要获得具有特定秩的行，不应该手工计算秩，而是使用限制/抵消条款

SELECT ...
FROM ATP
ORDER BY field21, id
LIMIT 1 OFFSET x

这仍然需要对所有表行进行排序，以确定哪个是x-，但比多个嵌套表扫描效率高得多。