On 31 Jul 2014, at 1:41pm, Ketil Froyn <[email protected]> wrote:
> I find it would be
> very useful to log some or all queries sent to the database, ideally also
> with timing information on how long it took to gather and send the
> response, so that it can be determined what queries are received and how
> long each query actually takes.
This probably wouldn't get built into the standard SQLite library. Bear in
mind that of the literally billions of installations of SQLite, the vast
majority are on smartphones, DVRs, set-top boxes, embedded controllers, or
other small devices. So adding code to SQLite is rarely done just for things
which would be done only on 'normal' computers.
For the people who want it, it's easy to build a shell around the SQLite calls
like this:
int pseudo_sqlite3_exec(commandString) {
log commandString
log start time
int theResult = sqlite3_exec(commandString)
log finish time
return theResult
}
Log to a text file, and write a utility to analyse the text file later.
SQLite does, however, include calls which can be used to monitor changes made
to the database:
<http://www.sqlite.org/capi3ref.html#sqlite3_update_hook>
<http://www.sqlite.org/capi3ref.html#sqlite3_commit_hook>
SQLite also includes a command specifically made for spotting badly-optimized
combinations of commands and databases:
<http://www.sqlite.org/eqp.html>
You can feed any statement to this and /instead of executing it/ SQLite will
list the steps it would take to execute it. So you can use it for testing
without having to worry that your database will be changed. If the query plan
for a commonly used command includes scanning a long table (rather that using
an index, which is shown differently) you think about and rephrasing it or
creating a good index for it.
You can even execute EXPLAIN QUERY PLAN in the SQLite shell tool, which outputs
the results neatly, and means you don't have to hack up your own software just
for testing purposes.
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
