使用Python压缩Sqlite中的XML列速度很慢！

Question

我刚开始学习Python和Sqlite，所以我知道可能有更好的方法来做这件事。我有一个数据库，里面有6000行数据，其中一列是一个14K的XML字符串。我想把这些XML字符串压缩一下，以便让数据库变得更小。不幸的是，下面这个脚本的运行速度比这个简单的命令行慢得多（命令行只需要几秒钟）。

sqlite3 weather.db .dump | gzip -c > backup.gz

我知道这两者不完全一样，但它确实是把数据库读出来并转换成文本，然后运行gzip。所以我原本希望这个脚本的性能能在10倍之内，但实际上慢了大约1000倍。有没有办法让下面这个脚本更高效一些呢？ 谢谢。

import zlib, sqlite3

conn = sqlite3.connect(r"weather.db")
r = conn.cursor()
w = conn.cursor()
rows = r.execute("select date,location,xml_data from forecasts")
for row in rows:
    data = zlib.compress(row[2])
    w.execute("update forecasts set xml_data=? where date=? and location=?", (data, row[0], row[1]))

conn.commit()
conn.close()

Answer 1

不太确定在事后进行更新是否能提高性能。因为在压缩和更新记录之间会有很多额外的开销。如果不在更新后进行清理，你是不会节省任何空间的。最好的办法可能是在第一次插入记录时就进行压缩。这样你不仅能节省空间，而且性能下降的感觉也不会那么明显。如果你不能在插入时进行压缩，那我觉得你已经尝试了这两种方法，并看到了结果。

Answer 2

你在这里比较的是完全不同的东西。sqlite3|gzip和Python版本之间最大的区别是，后者会把修改的内容写回数据库！

sqlite3|gzip的工作流程是：

• 读取数据库
• 对文本进行gzip压缩

而Python版本除了以上步骤，还会把压缩后的文本一条一条地更新回数据库。

Answer 3

抱歉，你的代码里是不是隐含地开始了一个事务？如果你在每次更新后都自动提交，这样会让你的速度变得很慢。

你在日期和/或位置上有没有合适的索引？这些列的数据变化情况怎么样？你能在这个表里使用自动编号的整数主键吗？

最后，你能分析一下你在zlib调用上花了多少时间，以及在更新上花了多少时间吗？除了数据库写入会让这个过程变慢之外，你的数据库版本还涉及到6000次调用（和6000次初始化）压缩算法。