Is there a tool for profiling sqlite queries?

Question

I use the SQLite database and want to speed up my queries, perhaps by using indexes or by restructuring them.

Is there a query profiling tool that can help me decide where things slow down?

I know that I can just type queries into a tool such as SQLite admin to wait, but I'm looking for something more systematic than this - maybe 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.

Answer 1

Here you have a lot of questions. To see what queries are being executed and how long it will take, you need to either change sqlite3.dll if the application refers to this, or if your own application you can write it to your code easier (we do this all the time, requests, transactions, timings etc.).

For individual query analysis, you can use EXPLAIN . It will not tell you the time frame of the individual steps in the request, but it will tell you how the request was completed.

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

The SQL statement may be preceded by the keyword "EXPLAIN" or the phrase "EXPLAIN QUERY PLAN". Any modification causes the SQL statement to behave like a query and return information about how the SQL statement would work if the EXPLAIN keyword or phrase was omitted.

When the EXPLAIN keyword appears on its own, it forces the operator to behave like a query that returns the sequence of virtual machine commands that it would use to execute the command if the EXPLAIN keyword were not present. When the phrase EXPLAIN QUERY PLAN appears, the statement returns high-level information about which indexes would be used.

Exiting EXPLAIN and EXPLAIN QUERY PLAN is for interactive analysis and troubleshooting only. Details of the output format can be changed from one SQLite release to another. Applications should not use EXPLAIN or EXPLAIN QUERY PLAN, since their exact behavior is undocumented, undefined, and variable.

Answer 2

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

I was looking for this because I was looking for something in time and the sqlite3 client has a meta timer command.

sqlite> .timer on

from there, all query results will have processor timer statistics. Hope this helps at least a little.

Answer 3

SQLite now has experimental sqlite3_trace and sqlite3_profile (for more details see https://www.sqlite.org/c3ref/profile.html ). They can come in handy for getting statistics / investigating the culprit during lengthy tests.