在SQLite中sqlite3.connect和关闭有多贵？(How expensive sqlite3.connect and close in SQLite?)

我使用connect（）和cursor（）来使用SQLite

self.connector = sqlite3.connect(self.dbFile) self.cursor = self.connector.cursor()

添加

我测试了以下简单的代码。 proc1（）使用在运行查询时始终打开和关闭的代码，proc2（）仅运行一次。

from sqlite import * import timeit import math def proc1(): db = SQLiteDB("./example.db", False) db.getOpenRunClose("SELECT * from Benchmark") db.getOpenRunClose("SELECT * from Benchmark") db.getOpenRunClose("SELECT * from Benchmark") db.getOpenRunClose("SELECT * from Benchmark") db.getOpenRunClose("SELECT * from Benchmark") db.getOpenRunClose("SELECT * from Benchmark") def proc2(): db = SQLiteDB("./example.db") res = db.runSQLToGetResult("SELECT * from Benchmark") res = db.runSQLToGetResult("SELECT * from Benchmark") res = db.runSQLToGetResult("SELECT * from Benchmark") res = db.runSQLToGetResult("SELECT * from Benchmark") res = db.runSQLToGetResult("SELECT * from Benchmark") res = db.runSQLToGetResult("SELECT * from Benchmark") db.close() if __name__ == '__main__': t = timeit.Timer(proc1) count = 5000 print t.timeit(count) / count t = timeit.Timer(proc2) count = 5000 print t.timeit(count) / count

ADDED

I tested with the following simple code. proc1() uses the code that opens and closes all the time when it runs the query, and proc2() runs only once.

from sqlite import * import timeit import math def proc1(): db = SQLiteDB("./example.db", False) db.getOpenRunClose("SELECT * from Benchmark") db.getOpenRunClose("SELECT * from Benchmark") db.getOpenRunClose("SELECT * from Benchmark") db.getOpenRunClose("SELECT * from Benchmark") db.getOpenRunClose("SELECT * from Benchmark") db.getOpenRunClose("SELECT * from Benchmark") def proc2(): db = SQLiteDB("./example.db") res = db.runSQLToGetResult("SELECT * from Benchmark") res = db.runSQLToGetResult("SELECT * from Benchmark") res = db.runSQLToGetResult("SELECT * from Benchmark") res = db.runSQLToGetResult("SELECT * from Benchmark") res = db.runSQLToGetResult("SELECT * from Benchmark") res = db.runSQLToGetResult("SELECT * from Benchmark") db.close() if __name__ == '__main__': t = timeit.Timer(proc1) count = 5000 print t.timeit(count) / count t = timeit.Timer(proc2) count = 5000 print t.timeit(count) / count

The result is as follows.

0.00157478599548 0.000539195966721

最满意答案

连接相当昂贵 - 它们对应于打开文件 - 但是游标并没有像你需要的那么多地使用^[1] 。什么是开销，特别是在插入或更新时（或者如果您创建表或索引，即使您处于自动提交模式），也会进行提交。这是因为数据库引擎在完成提交之前必须将数据同步到磁盘（这是提供持久性保证所必需的），而这在现代硬件上只是非常昂贵。（事务开始成本，因为它们需要对数据库文件进行一些锁定，这可能会产生影响。）

报表汇编也可能花费一些; 如果可能，重用编译语句。当然，无论如何，你应该这样做。为什么？这是因为你永远不应该将用户数据放在生成的SQL中; 这不仅会导致SQL注入漏洞的问题，而且还会强制数据库引擎在每次运行时重新编译语句。编译语句既安全又可能（更快）。

^[1]当然，使用更多的游标比你需要的更愚蠢。这只是浪费时间和精力。

Connections are fairly expensive – they correspond to opening the file – but cursors aren't very so use as many as you need^[1]. What does cost is transaction starts and especially commits when there's an insert or update (or if you create a table or index, of course) even if you're in auto-commit mode. That's because the database engine has to sync the data to disk before it finishes the commit (required for a durability guarantee) and that's just plain expensive on modern hardware. (Transaction starts cost because they require doing some locking of the DB file, which can have an impact.)

Compilation of statements can also cost a bit; reuse compiled statements if possible. Of course, you should be doing that anyway. Why? It's because you should never put user data in generated SQL; not only does that lead to trouble with SQL injection vulnerabilities, but it also forces the DB engine to recompile the statement every time you run it. Compiled statements are both safer and (probably) faster too.

^[1] Of course, it's silly to use more cursors than you need. That's just plain wasting time and effort.

更多推荐