我正在使用pythons内置的sqlite3
模块访问数据库。我的查询在一个包含150000个条目的表和一个包含40000个条目的表之间执行连接,结果再次包含大约150000个条目。如果我在SQLite Manager中执行查询,则需要几秒钟,但如果我在Python中执行相同的查询,一分钟后它还没有完成。下面是我使用的代码:
cursor = self._connection.cursor()
annotationList = cursor.execute("SELECT PrimaryId, GOId " +
"FROM Proteins, Annotations " +
"WHERE Proteins.Id = Annotations.ProteinId")
annotations = defaultdict(list)
for protein, goterm in annotationList:
annotations[protein].append(goterm)
我做fetchall
只是为了测量执行时间。有人能解释一下性能上的巨大差异吗?我在Mac OS X 10. 6. 4上使用的是Python 2. 6. 1。
我手动实现了连接,这样做的速度要快得多。代码如下所示:
cursor = self._connection.cursor()
proteinList = cursor.execute("SELECT Id, PrimaryId FROM Proteins ").fetchall()
annotationList = cursor.execute("SELECT ProteinId, GOId FROM Annotations").fetchall()
proteins = dict(proteinList)
annotations = defaultdict(list)
for protein, goterm in annotationList:
annotations[proteins[protein]].append(goterm)
所以当我自己获取表,然后用Python进行连接时,大约需要2秒,而上面的代码则需要很长时间,我是不是漏掉了什么?
我用apsw做了同样的尝试,它工作得很好(代码根本不需要修改),性能很好。我仍然想知道为什么sqlite3
-模块这么慢。
3条答案
按热度按时间p1tboqfb1#
这里有一个关于它的讨论:http://www.mail-archive.com/python-list@python.org/msg253067.html
sqlite3模块似乎出现了性能瓶颈,有一个advice如何让你的查询更快:
hc8w905p2#
您还没有发布所讨论的表的模式,但我认为索引可能有问题,特别是没有Protein.id或Annotations.ProteinId(或两者)的索引。
按如下方式创建SQLite索引
velaa5lx3#
我想更新这个,因为我注意到同样的问题,我们现在是2022年...
在我自己的应用程序中,我使用python3和sqlite3对大型数据库进行一些数据处理(〉100000行 *〉200列)。特别是,我注意到我的3表内部连接在Python中运行大约12分钟,而从CLI在sqlite3中运行相同的连接查询大约需要100秒。所有的连接 predicate 都被正确地索引,并且EXPLAIN QUERY PLAN指示增加的时间很可能是因为我使用了SELECT *,这在我的特定上下文中是必要的。
性能差异导致我整晚都在揪头发,直到我意识到从这里有一个快速修复:Running a Sqlite3 Script from Command Line.这绝对是一个变通办法,在最好的,但我有研究到期,所以这是我的修复。
1.将查询写出到一个.sql文件(我使用f字符串传入变量,所以这里使用了一个带有{foo}的示例)
1.从python内部运行os.system并将.sql文件发送到sqlite3
确保在运行之前关闭所有打开的连接,这样你就不会被锁定,如果你要在python中使用sqlite,你就必须重新示例化所有的连接对象。
希望这有帮助,如果有人已经弄清楚了这个来源,请链接到它!