Is there a tool to profile sqlite queries?

问题:

I am using a SQLite database and would like to speed up my queries, perhaps with indexes or by restructuring them altogether.

Is there a tool to profile queries, that might help me decide where things are slowing down?

I know I could just enter queries into a tool like SQLite Administrator to time them, but I'm looking for something a bit more systematic than that -- perhaps something that sits in the background and looks at all the queries that I enter over a period, giving a breakdown of any bottle necks.

回答1:

You have a mix of questions in here. To view what queries are run and how long each takes, you'll need to either modify sqlite3.dll if an application is linking to that or if it's your own application you can write it into your code easier (we do this and long all queries, transactions, timings, etc.).

For individual query analysis, you can use EXPLAIN. It won't tell you timing of individual steps within a query but it will tel you how the query was executed.

http://www.sqlite.org/lang_explain.html

An SQL statement can be preceded by the keyword "EXPLAIN" or by the phrase "EXPLAIN QUERY PLAN". Either modification causes the SQL statement to behave as a query and to return information about how the SQL statement would have operated if the EXPLAIN keyword or phrase had been omitted.

When the EXPLAIN keyword appears by itself it causes the statement to behave as a query that returns the sequence of virtual machine instructions it would have used to execute the command had the EXPLAIN keyword not been present. When the EXPLAIN QUERY PLAN phrase appears, the statement returns high-level information about what indices would have been used.

The output from EXPLAIN and EXPLAIN QUERY PLAN is intended for interactive analysis and troubleshooting only. The details of the output format are subject to change from one release of SQLite to the next. Applications should not use EXPLAIN or EXPLAIN QUERY PLAN since their exact behavior is undocumented, unspecified, and variable.

回答2:

This will only answer one part of the question (the most unhelpful part, unfortunately).

I googled this up because I was looking for something to time queries and the sqlite3 client has a timer meta command.

sqlite> .timer on

from there on in, all query results will have cpu timer statistics appended. Hope this helps at least a little bit.

回答3:

Now SQLite has experimental sqlite3_trace and sqlite3_profile (see https://www.sqlite.org/c3ref/profile.html for details). They can come in handy for having statistics/investigating culprit during long tests.