Hi all,
I would like to gain more knowledge on the performance measurments and (more specifically) their possible implications and maybe
some Database theory to boot.
The figures of interest are the statement quantitative values which return simple counters such as Table scans, Virtual Machine
operations, Sort operations and assist Indices.
For reference, these figures are extractable through the c interface:
sqlite3_stmt_status(sqlite3_stmt*, int op,int resetFlg);
which can be called with these flags to read the values mentioned above:
#define SQLITE_STMTSTATUS_FULLSCAN_STEP 1
#define SQLITE_STMTSTATUS_SORT 2
#define SQLITE_STMTSTATUS_AUTOINDEX 3
#define SQLITE_STMTSTATUS_VM_STEP 4
Firstly - Reading these values give different (sometimes widely different) results for the same query being run successively. I am
imagining some clever caching being the cause of that, but do not wish to make erroneous assumptions - if someone could kindly weigh
in on this.
Secondly, A query that seemingly requires no sorting sometimes give quite a large figure for the Sort-Ops, what is it about this
that I am not understanding?
Also - Any ideas on how the Query optimiser decides to add autoindexes to assist in a query, and did this change with the advent of
the NGQP?
Lastly - I would appreciate it very much if someone can speak a bit about the theoretical implications of these values. For instance
what high table scans vs. low VM steps might mean, or the opposite... if any of it can be used to gauge a good query, or at least to
flag a possibly bad query or one that could benefit from better indexing.
I know this question is one of those that requires some knowledge and will probably take more than a few seconds to answer and maybe
not so interesting to most users, so apologies for that, but I find it fascinating and it would be very much appreciated.
Thank you kindly
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
