假设我有这样的查询

 字符串SQL =SELECT s.team_id，s.team_name，s.gp，SW，ST，SL，s.go，s.ga，s.score，SP，由
           +（选择TEAM_ID，TEAM_NAME，SUM（GP）GP，SUM（W）W，SUM（T）T，SUM（L）L，SUM（GO）去，SUM（GA）GA，SUM（GO） -  SUM （GA）得分，SUM（2×宽+ T）p FROM
           +（选择t._id TEAM_ID，t.name TEAM_NAME，COUNT（CASE WHEN score_home IS NOT NULL，则1 END）金币，COUNT（CASE WHEN score_home＆GT; score_away THEN 1 END）W，
           +COUNT（CASE WHEN score_home = score_away THEN 1 END）T，COUNT（CASE WHEN score_home＆LT; score_away THEN 1 END）L
           +SUM（score_home）去，SUM（score_away）が
           +FROM team_table牛逼LEFT OUTER JOIN match_table M于m.team_home = t._id
           +WHERE t.tournament_id =？GROUP BY t._id，t.name
           +UNION ALL
           +选择t._id TEAM_ID，t.name TEAM_NAME，COUNT（CASE WHEN score_away IS NOT NULL，则1 END）金币，COUNT（CASE WHEN score_home＆LT; score_away THEN 1 END）W，
           +COUNT（CASE WHEN score_home = score_away THEN 1 END）T，COUNT（CASE WHEN score_home＆GT; score_away THEN 1 END）L
           +SUM（score_away）去，SUM（score_home）が
           +FROM team_table牛逼LEFT OUTER JOIN match_table M于m.team_away = t._id
           +WHERE t.tournament_id =？GROUP BY t._id，t.name）
           +GROUP BY TEAM_ID，TEAM_NAME）S
           +ORDER BY s.p DESC，s.score DESC，s.go ASC;
 

然后用于这样

 光标光标= database.rawQuery（SQL，参数）;

cursor.moveToFirst（）;
而（！cursor.isAfterLast（））{
    TeamStats STAT =新TeamStats（）;

    stat.setTeamId（cursor.getLong（0））;
    stat.setTeamName（cursor.getString（1））;
    stat.setGamesPlayed（cursor.getInt（2））;
    stat.setWins（cursor.getInt（3））;
    stat.setTies（cursor.getInt（4））;
    stat.setLoses（cursor.getInt（5））;
    stat.setGoalsOwn（cursor.getInt（6））;
    stat.setGoalsAgaist（cursor.getInt（7））;
    stat.setScore（cursor.getInt（8））;
    stat.setPoints（cursor.getInt（9））;

    stats.add（STAT）;
    cursor.moveToNext（）;
}
cursor.close（）;
 

所以它会选择从多个表中的值，做一些操作等等。正如你所看到的查询是可怕的复杂的（很难调试），并表现似乎并没有那么好，因为我期望的那样。我的问题是：

我可以改善服务表现使用某种prepared声明？ 难道是更快的执行更简单的查询，并与一些自定义的code手动处理它们？ 解决方案

如果我是你，我会复制你的SQLite数据库来托管，然后尝试在一些SQLite的图形用户界面手动执行它，而更换绑定变量（？）实际变量的值，你有。对于图形用户界面在Windows上，我真的很喜欢 SQLite的专家个人和Linux上的 sqliteman 是pretty的好。

在调试您的SQL（在命令行或GUI），一定要通过运行它们在 EXPLAIN来分析SQL语句和/或 EXPLAIN查询规划 。当心表扫描。你应该尝试通过添加索引，以消除昂贵的扫描。但是不要指数一切 - 它可能使事情变得更糟。 通常情况下，你可以通过使用复合（多列）索引的大幅性能提升。请注意，在任何给定表的SQLite不能使用不止一个索引（在运行给定的SQL语句） - 所以，明智地选择你的索引。 （另见查询规划的基本解释。）

和解决您的有关数据处理的担忧在Java中对SQLite的 - 我认为这完全优化（与适当的索引等）的SQLite查询的对关系数据的将（几乎）总是会比在Java中该数据的手工处理速度更快。这必须是尤其如此，你的情况 - 所有的数据基本上是关系

一个小提醒，但：您的Andr​​oid APK使用Java可以访问超过SQLite的更多的内存，并在默认情况下 - 你可能想用增加的SQLite缓存大小为您的数据库 setMaxSqlCacheSize（） （ PRAGMA CACHE_SIZE相当于）。 Android的默认值是10（最大100），尝试提高一下，看看是否让你查询任何区别。需要注意的是台式机的SQLite默认这个设置要高得多 - 2000

Suppose I have this kind of query

String sql = "SELECT s.team_id, s.team_name, s.gp, s.w, s.t, s.l, s.go, s.ga, s.score, s.p FROM "
           + "(SELECT team_id, team_name, SUM (gp) gp, SUM (w) w, SUM (t) t, SUM (l) l, SUM (GO) go, SUM (GA) ga, SUM (GO)- SUM (GA) score, SUM (2*w+t) p FROM "
           + "(SELECT t._id team_id, t.name team_name, COUNT(CASE WHEN score_home IS NOT NULL THEN 1 END) gp, COUNT (CASE WHEN score_home > score_away THEN 1 END) w,"
           + " COUNT (CASE WHEN score_home = score_away THEN 1 END) t, COUNT (CASE WHEN score_home < score_away THEN 1 END) l,"
           + " SUM (score_home) go, SUM (score_away) ga"
           + " FROM team_table t LEFT OUTER JOIN match_table m ON m.team_home = t._id"
           + " WHERE t.tournament_id = ? GROUP BY t._id, t.name"
           + " UNION ALL"
           + " SELECT t._id team_id, t.name team_name, COUNT(CASE WHEN score_away IS NOT NULL THEN 1 END) gp, COUNT (CASE WHEN score_home < score_away THEN 1 END) w,"
           + " COUNT (CASE WHEN score_home = score_away THEN 1 END) t, COUNT (CASE WHEN score_home > score_away THEN 1 END) l,"
           + " SUM (score_away) go, SUM (score_home) ga"
           + " FROM team_table t LEFT OUTER JOIN match_table m ON m.team_away = t._id"
           + " WHERE t.tournament_id = ? GROUP BY t._id, t.name)"
           + " GROUP BY team_id, team_name) s"
           + " ORDER BY s.p DESC, s.score DESC, s.go ASC";

which is then used like this

Cursor cursor = database.rawQuery(sql, args);

cursor.moveToFirst();
while (!cursor.isAfterLast()) {
    TeamStats stat = new TeamStats();

    stat.setTeamId(cursor.getLong(0));
    stat.setTeamName(cursor.getString(1));
    stat.setGamesPlayed(cursor.getInt(2));
    stat.setWins(cursor.getInt(3));
    stat.setTies(cursor.getInt(4));
    stat.setLoses(cursor.getInt(5));
    stat.setGoalsOwn(cursor.getInt(6));
    stat.setGoalsAgaist(cursor.getInt(7));
    stat.setScore(cursor.getInt(8));
    stat.setPoints(cursor.getInt(9));

    stats.add(stat);
    cursor.moveToNext();
}
cursor.close();

So it selects values from many tables, does some operations etc. As you can see the query is horribly complex (very difficult to debug) and the performance does not seem to be as good as I would expect. My questions are:

Can I improve performace using some kind of prepared statement? Would it be faster to perform more simple queries and process them manually with some custom code?

解决方案

If I were you, I would copy your sqlite database to host, then try to execute it manually in some SQLite GUI while replacing bound variables (?) with actual variable values you have. For GUI on Windows, I really like SQLite Expert Personal, and on Linux sqliteman is pretty good.

While debugging your SQL (in command line or GUI), be sure to analyze your SQL statements by running them under EXPLAIN and/or EXPLAIN QUERY PLAN. Watch out for table scans. You should try to eliminate expensive scans by adding indexes. But don't index everything - it may make things worse. Often, you can have big performance gains by using compound (multi-column) indexes. Note, that on any given table SQLite cannot make use of more than just one index (while running given SQL statement) - so, choose your indexes wisely. (See also the basic explanation in Query Planning.)

And to address your concerns about data processing in Java vs. SQLite - I think that fully optimized (with proper indexes, etc.) SQLite query against relational data will (almost) always going to be faster than manual processing of this data in Java. This must be especially true in your case - all your data is basically relational.

One small note though: Your Android APK using Java may have access to more memory than SQLite does by default - you may want to increase SQLite cache size for your database using setMaxSqlCacheSize() (equivalent of PRAGMA cache_size). Android default is 10 (max 100), try increasing it and see if makes any difference for your query. Note that desktop SQLite default for this setting is much higher - 2000.