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.
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
Now SQLite has experimental
sqlite3_trace
andsqlite3_profile
(see https://www.sqlite.org/c3ref/profile.html for details). They can come in handy for having statistics/investigating culprit during long tests.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.