使用JDBC进行SQLite查询要比使用Firefox SqliteManager插件慢得多 [英] SQLite queries much slower using JDBC than in Firefox SqliteManager plugin
问题描述
我在Firefox的SQLiteManager插件中测试过的查询存在问题.
这个特定的查询在Firefox插件中执行大约60毫秒，但是当我使用最新的Sqlite JDBC驱动程序在Java中使用相同的查询和相同的数据库时，执行此查询要花费高达3.7秒的时间.
对于其他运行良好的查询，Firefox插件通常会快一点(顶部快50ms，有时JDBC快)，但这可能是创建连接并将结果添加到列表的开销，但是该特定查询的性能差异简直太荒谬了.
这是查询:
SELECT p1.Id, p1.FirstName || ' ' || p1.LastName AS PlayerName, sch1.LaneNum, l1.Name AS LeagueName, l1.Season, SUM(s1.Score) AS Series, e1.Date FROM Scores s1
JOIN SchedulePlayers sp1 ON s1.SchedulePlayerId = sp1.Id
JOIN Schedules sch1 ON sp1.ScheduleId = sch1.Id
JOIN Players p1 ON sp1.PlayerId = p1.Id
JOIN TeamEncounters te1 ON sch1.TeamEncounterId = te1.Id
JOIN Encounters e1 ON te1.EncounterId = e1.Id
JOIN Leagues l1 ON e1.LeagueId = l1.Id
WHERE s1.GameNum < 4 AND l1.Name LIKE 'Juniors%' AND l1.Season = 2013 AND (sch1.LaneNum = 1 OR sch1.LaneNum = 2) AND s1.IsBowlout = 0
GROUP BY p1.Id, l1.Id, e1.Id
ORDER BY Series DESC LIMIT 0,20
很明显，最慢的部分是"LIKE'Juniors％'"，但这不能解释为什么它在Java中而不是在插件中很慢.
如果执行 EXPLAIN QUERY PLAN ，我看到firefox插件对Leagues表使用了以下索引:列:季节，名称，实名"(此查询中尚未使用实名) ).
如果我在Java中执行 EXPLAIN QUERY PLAN ，则Leagues表使用的索引是INTEGER PRIMARY KEY索引，这是我认为问题所在.
在Java中，我运行上述查询，然后使用相同的连接运行两次相同的查询，但是用 p1代替了 l1.Name LIKE'Juniors％部分.Sex = 1 和 p1.Sex = 2 第二次.在这两种情况下，最后两个查询都很快速，这进一步证明了问题出在 l1.Name LIKE'Juniors％'
我在所有表上都有主键，在所有需要它的列上都有外键.我还有许多其他索引，因为我要从头开始重新设计旧数据库，因为有很多重复的字段，因此我决定添加索引以使其更快，但是在这种特殊情况下，我陷入了困境，尤其是因为它可以在一个数据库中工作情况，但不是另一个.我是否可能对表进行过分索引，这使Manager很难选择正确的索引?
随时询问有关表，列，查询等的更多信息.
编辑
Firefox插件使用SQLite 3.7.17，而JDBC驱动程序使用SQLite 3.8.0.我尝试使用3.7.20 JDBC驱动程序(找不到指向3.7.17驱动程序的下载链接)，并且遇到了相同的性能问题，并且其他一些查询的性能也较差，因此我改回3.8. .0.
我编辑性能时间是因为基准测试时我犯了一个错误:以前的时间是多次运行查询.因此，在Firefox中，执行一次查询大约需要60毫秒，而在Java中，则需要3600毫秒，因此要执行查询的时间增加了60倍，这对于我的应用程序是不可接受的.
这是Java查询执行中的详细说明计划，其中各列的顺序为:SelectId，Order，From，Detail:
0 0 0 SEARCH TABLE Scores AS s1 USING INDEX idxScoresGameNumScore (GameNum<?)
0 1 1 SEARCH TABLE SchedulePlayers AS sp1 USING INTEGER PRIMARY KEY (rowid=?)
0 2 3 SEARCH TABLE Players AS p1 USING INTEGER PRIMARY KEY (rowid=?)
0 3 2 SEARCH TABLE Schedules AS sch1 USING INTEGER PRIMARY KEY (rowid=?)
0 0 0 EXECUTE LIST SUBQUERY 1
0 4 4 SEARCH TABLE TeamEncounters AS te1 USING INTEGER PRIMARY KEY (rowid=?)
0 5 5 SEARCH TABLE Encounters AS e1 USING INTEGER PRIMARY KEY (rowid=?)
0 6 6 SEARCH TABLE Leagues AS l1 USING INTEGER PRIMARY KEY (rowid=?)
0 0 0 USE TEMP B-TREE FOR GROUP BY
0 0 0 USE TEMP B-TREE FOR ORDER BY
如您所见，Leagues使用整数主键，因此它完全忽略其中带有名称"的索引.
Firefox插件的解释查询计划为:
0 0 6 SEARCH TABLE Leagues AS l1 USING COVERING INDEX idxLeaguesRealName (Season=?) (~19 rows)
0 1 5 SEARCH TABLE Encounters AS e1 USING INDEX idxEncounters (LeagueId=?) (~16 rows)
0 2 4 SEARCH TABLE TeamEncounters AS te1 USING AUTOMATIC COVERING INDEX (EncounterId=?) (~6 rows)
0 3 2 SEARCH TABLE Schedules AS sch1 USING INDEX sqlite_autoindex_Schedules_1 (TeamEncounterId=?) (~1 rows)
0 4 1 SEARCH TABLE SchedulePlayers AS sp1 USING COVERING INDEX idxSchedulePlayers (ScheduleId=?) (~6 rows)
0 5 3 SEARCH TABLE Players AS p1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0 6 0 SEARCH TABLE Scores AS s1 USING INDEX sqlite_autoindex_Scores_1 (SchedulePlayerId=? AND GameNum<?) (~1 rows)
0 0 0 USE TEMP B-TREE FOR GROUP BY
0 0 0 USE TEMP B-TREE FOR ORDER BY
如您所见，表的顺序也不相同，实际上，所有表都使用Java中的PRIMARY KEY索引，而它们使用Java中似乎更好的索引， ，我认为这很奇怪.
* 我在JOIN Leagues l1之后尝试使用INDEXED BY idxLeaguesRealName * ，但是性能保持不变(可能是因为SEARCH TABLE Leagues在Java中位于底部而不是第一个表). /p>
idxLeaguesRealName是Season，Name，RealName的索引，根据此链接由@CL发布.是低质量指标，因为在230个不同的联赛中，赛季仅需采用4个不同的值.我已经在运行查询之前运行了 ANALYZE 命令，因此，根据该链接，它应该可以解决使用低质量索引的问题.
我尝试的另一件事是创建一个新索引，该索引也使用主键字段(例如:Id，Season，Name)，但是查询计划程序不使用它. 我什至不知道将主键作为用户创建的索引中的字段之一是个好主意.我只是想尽一切办法，因为我在这里迷失了方向，因为我不了解两种运行查询方式之间的性能差异.
关于几乎相同的其他查询的更多信息
正如我前面提到的，我运行其他查询几乎相同，只是将 l1.Name LIKE'Juniors％'替换为 p1.Sex = 1 或 p1.Sex = 2 .这些查询在Firefox中的执行时间分别为62毫秒，而在Java中的执行时间为52毫秒，这意味着查询计划者可以很好地完成此类查询.
在JDBC中，EXPLAIN QUERY PLAN给出以下输出:
0 0 4 SCAN TABLE TeamEncounters AS te1 USING COVERING INDEX idxTeamEncounters
0 1 5 SEARCH TABLE Encounters AS e1 USING INTEGER PRIMARY KEY (rowid=?)
0 2 2 SEARCH TABLE Schedules AS sch1 USING INDEX sqlite_autoindex_Schedules_1 (TeamEncounterId=?)
0 0 0 EXECUTE LIST SUBQUERY 1
0 3 6 SEARCH TABLE Leagues AS l1 USING INTEGER PRIMARY KEY (rowid=?)
0 4 1 SEARCH TABLE SchedulePlayers AS sp1 USING COVERING INDEX idxSchedulePlayers (ScheduleId=?)
0 5 3 SEARCH TABLE Players AS p1 USING INTEGER PRIMARY KEY (rowid=?)
0 6 0 SEARCH TABLE Scores AS s1 USING INDEX sqlite_autoindex_Scores_1 (SchedulePlayerId=? AND GameNum<?)
0 0 0 USE TEMP B-TREE FOR GROUP BY
0 0 0 USE TEMP B-TREE FOR ORDER BY
与原始查询的计划有很大不同，因为与其他情况一样，该查询使用的索引似乎比仅使用PRIMARY KEYs索引更有意义.
我刚刚检查了一下，在我的应用程序中还有其他查询执行缓慢.所有慢速查询都是带有'l1.Name LIKE'Juniors％'的查询，其他所有查询都运行得很快.
我已阅读到使用 Like 的查询运行缓慢，这将使我改变设计某些表的方式，例如添加字段"IsJuniorLeague"并与之进行比较，这可能会解决问题，但是由于我已经看到可以像Firefox插件中那样使查询变得足够快，所以我真的很想了解幕后情况，因为我通常先在firefox中测试查询，然后再在应用程序中对其进行尝试，因为这样更快.
差异可能是由于不同的SQLite版本引起的.
(用SELECT sqlite_version();检查.)
阅读优化程序清单.
在此特定查询中，您可以通过编写以下内容来强制使用索引:
... JOIN Leagues l1 INDEXED BY MyThreeColumnIndex ON ...
I'm having a problem with a query that I tested in the SQLiteManager plugin in Firefox.
This particular query executes in about 60 ms in the Firefox plugin, but it takes a whopping 3.7 seconds to execute when I use the same query and the same database in Java using the latest Sqlite JDBC driver.
The Firefox plugin is often a little bit faster for my other queries that work well (50ms faster at the top, sometimes JDBC is faster), but that's probably the overhead of creating the connection and adding the results to a list, but this particular query's performance difference is just ridiculous.
Here is the query:
SELECT p1.Id, p1.FirstName || ' ' || p1.LastName AS PlayerName, sch1.LaneNum, l1.Name AS LeagueName, l1.Season, SUM(s1.Score) AS Series, e1.Date FROM Scores s1
JOIN SchedulePlayers sp1 ON s1.SchedulePlayerId = sp1.Id
JOIN Schedules sch1 ON sp1.ScheduleId = sch1.Id
JOIN Players p1 ON sp1.PlayerId = p1.Id
JOIN TeamEncounters te1 ON sch1.TeamEncounterId = te1.Id
JOIN Encounters e1 ON te1.EncounterId = e1.Id
JOIN Leagues l1 ON e1.LeagueId = l1.Id
WHERE s1.GameNum < 4 AND l1.Name LIKE 'Juniors%' AND l1.Season = 2013 AND (sch1.LaneNum = 1 OR sch1.LaneNum = 2) AND s1.IsBowlout = 0
GROUP BY p1.Id, l1.Id, e1.Id
ORDER BY Series DESC LIMIT 0,20
Obviously, the slow part is the "LIKE 'Juniors%'", but that doesn't explain why it is slow in Java and not in the plugin.
If I execute EXPLAIN QUERY PLAN, I see that the firefox plugin uses the following index for the Leagues table: Columns: "Season, Name, RealName" (RealName is not used in this query yet).
If I execute EXPLAIN QUERY PLAN in Java, the index that is used for the Leagues table is the INTEGER PRIMARY KEY index, which is where I think the problem is.
In java, I run the above query, and then using the same connection I run the same query two more times, but replacing the l1.Name LIKE 'Juniors% part by p1.Sex = 1 and p1.Sex = 2 the second time. Those last two queries are fast in both cases, which further proves that the problem comes from the l1.Name LIKE 'Juniors%'
I have primary keys on all tables and foreign keys on all columns that require it. I also have many other indexes because I am redesigning the old database from scratch because there were many repeated fields and I decided to add indexes to make it even faster, but in this particular case, I'm stuck, especially since it works in one case but not another. Is it possible that I indexed the tables too aggressively and it makes it more difficult for the Manager to pick the correct indexes?
Feel free to ask for more information about the tables, columns, queries, etc.
EDIT
The Firefox plugin uses SQLite 3.7.17 and the JDBC driver uses SQLite 3.8.0. I tried using the 3.7.20 JDBC driver (couldn't find a download link to the 3.7.17 driver) and I get the same performance issues, and some other queries get a worse performance with this one, so I switched back to 3.8.0.
I edited the performance times because I had made a mistake when benchmarking: the previous times were for running the queries multiple times. So in Firefox, it takes about 60 ms to execute the query once while in Java, it takes 3600 ms, so that's 60 times more, which is not acceptable for my application.
Here is the detailed EXPLAIN QUERY PLAN from the Java query execution, where the columns are, in order: SelectId, Order, From, Detail:
0 0 0 SEARCH TABLE Scores AS s1 USING INDEX idxScoresGameNumScore (GameNum<?)
0 1 1 SEARCH TABLE SchedulePlayers AS sp1 USING INTEGER PRIMARY KEY (rowid=?)
0 2 3 SEARCH TABLE Players AS p1 USING INTEGER PRIMARY KEY (rowid=?)
0 3 2 SEARCH TABLE Schedules AS sch1 USING INTEGER PRIMARY KEY (rowid=?)
0 0 0 EXECUTE LIST SUBQUERY 1
0 4 4 SEARCH TABLE TeamEncounters AS te1 USING INTEGER PRIMARY KEY (rowid=?)
0 5 5 SEARCH TABLE Encounters AS e1 USING INTEGER PRIMARY KEY (rowid=?)
0 6 6 SEARCH TABLE Leagues AS l1 USING INTEGER PRIMARY KEY (rowid=?)
0 0 0 USE TEMP B-TREE FOR GROUP BY
0 0 0 USE TEMP B-TREE FOR ORDER BY
As you can see, Leagues uses the integer primary key, so it completely ignores indices that have 'Name' in it.
The EXPLAIN QUERY PLAN for the Firefox plugin is:
0 0 6 SEARCH TABLE Leagues AS l1 USING COVERING INDEX idxLeaguesRealName (Season=?) (~19 rows)
0 1 5 SEARCH TABLE Encounters AS e1 USING INDEX idxEncounters (LeagueId=?) (~16 rows)
0 2 4 SEARCH TABLE TeamEncounters AS te1 USING AUTOMATIC COVERING INDEX (EncounterId=?) (~6 rows)
0 3 2 SEARCH TABLE Schedules AS sch1 USING INDEX sqlite_autoindex_Schedules_1 (TeamEncounterId=?) (~1 rows)
0 4 1 SEARCH TABLE SchedulePlayers AS sp1 USING COVERING INDEX idxSchedulePlayers (ScheduleId=?) (~6 rows)
0 5 3 SEARCH TABLE Players AS p1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0 6 0 SEARCH TABLE Scores AS s1 USING INDEX sqlite_autoindex_Scores_1 (SchedulePlayerId=? AND GameNum<?) (~1 rows)
0 0 0 USE TEMP B-TREE FOR GROUP BY
0 0 0 USE TEMP B-TREE FOR ORDER BY
As you can see, the order of the tables isn't the same either, and actually, all tables use the PRIMARY KEY index in Java, while they use what seem to be 'better' indices in Java, which I think is weird.
*I tried using INDEXED BY idxLeaguesRealName * after JOIN Leagues l1, but the performance stays the same (probably because the SEARCH TABLE Leagues is at the bottom in Java instead of the first table).
idxLeaguesRealName is an index on Season, Name, RealName, which, according to 5.3 in this link posted by @CL. is a low quality index because season only takes about 4 different values for 230 different leagues. I have run the ANALYZE command before running the queries , though, so according to that link, it should fix the problem of using a low quality index.
Another thing I tried is creating a new index that also uses the primary key field (for instance: Id, Season, Name), but the Query Planner doesn't use it. I don't even know if it's a good idea to put the primary key as one of the fields in an user-created index. I'm just trying everything I can think of, because I'm at lost here since I don't understand the performance difference between the two ways of running the queries.
EXTRA INFORMATION ON OTHER QUERIES THAT ARE ALMOST THE SAME
As I mentioned earlier, I run other queries that are almost the same, except that l1.Name LIKE 'Juniors%' is replaced by either p1.Sex = 1 or p1.Sex = 2. Those queries execute in about 62ms each in Firefox and in 52 ms in Java, which means the query planner does a good job on this similar query.
In JDBC, the EXPLAIN QUERY PLAN gives this output:
0 0 4 SCAN TABLE TeamEncounters AS te1 USING COVERING INDEX idxTeamEncounters
0 1 5 SEARCH TABLE Encounters AS e1 USING INTEGER PRIMARY KEY (rowid=?)
0 2 2 SEARCH TABLE Schedules AS sch1 USING INDEX sqlite_autoindex_Schedules_1 (TeamEncounterId=?)
0 0 0 EXECUTE LIST SUBQUERY 1
0 3 6 SEARCH TABLE Leagues AS l1 USING INTEGER PRIMARY KEY (rowid=?)
0 4 1 SEARCH TABLE SchedulePlayers AS sp1 USING COVERING INDEX idxSchedulePlayers (ScheduleId=?)
0 5 3 SEARCH TABLE Players AS p1 USING INTEGER PRIMARY KEY (rowid=?)
0 6 0 SEARCH TABLE Scores AS s1 USING INDEX sqlite_autoindex_Scores_1 (SchedulePlayerId=? AND GameNum<?)
0 0 0 USE TEMP B-TREE FOR GROUP BY
0 0 0 USE TEMP B-TREE FOR ORDER BY
Which is vastly different from the original query's plan, since this one uses indices that seem to make more sense than only using PRIMARY KEYs indices like in the other case.
I just checked, and there are other queries in my application that execute slowly. All the slow queries are the ones that have the 'l1.Name LIKE 'Juniors%', everything else runs really fast.
I have read that queries that use LIKE run slowly, which would make me switch the way I designed some tables, like adding a field 'IsJuniorLeague' and comparing to that instead, which would probably fix the issue, but since I have seen that it's possible to make those queries fast enough, like in the Firefox plugin, I really want to understand what's happening behind the scenes since I usually test my queries in firefox first before trying them in my application since it's faster that way.
The differences are likely due to different SQLite versions.
(Check with SELECT sqlite_version();.)
Read the optimizer checklist.
In this particular query, you can force usage of the index by writing:
... JOIN Leagues l1 INDEXED BY MyThreeColumnIndex ON ...
这篇关于使用JDBC进行SQLite查询要比使用Firefox SqliteManager插件慢得多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
