On 2014/11/14 16:32, RP McMurphy wrote:
--------------------------------------------
On Tue, 11/11/14, RP McMurphy<rpm0...@yahoo.com> wrote:
> If you can provide
any examples where ANALYZE makes a query slower, I
suspect the developer team would like
> to see them.
>
After we run analyze and then
let the process run for a while the DB
contents change - and it can change quite
considerably depending
upon what is
happening. I suspect that the analyze data gets stale, but
I
don't know how to track such things in
sqlite. Anyhow we can't keep running
analyze every few minutes because it takes a
long time to run with our DB
and it appears
to block all other actions until it is done.
Firstly, that is a considerably different problem to your initial claim that ANALYZE makes queries slower. That said, it is an
understandable concern. When you say the data changes, does the shape actually change? Because all that matters to analyze is the
shape of the data, not the data itself.
As an example, consider the following three small tables:
A:
ID, No, Name, Value, XT, AX
1, 1, John, 34, Panthera Leo, 2014-11-12 09:44:26
2, 2, Chris, 88, Panthera Pardis, 2014-11-13 04:17:43
3, 3, Martin, 20, etc...
B:
ID, No, Name, Value, XT, AX
1, 1, John, 34, ,
2, X1, John, , Panthera Leo,
3, A1, John, , , 2014-11-12 09:44:26
4, 2, Chris, 88, ,
5, X2, Chris, , Panthera Pardis,
6, A2, Chris, , , 2014-11-13 04:17:43
etc.
C:
ID, No, Name, Value, XT, AX
106, 62, Andrew, 18, Loxodonta Africana, 0000-00-00 00:00:00
107, 677, James, 12, Podocarpus Falcatus, 2014-11-09 14:27:12
108, 176612, Flemming, 40, Tectonia Grandis, 2014-11-14 03:31:55
etc.
From table A to B the data did not change significantly, but the shape of it changed significantly in respect of cardinality, value
types, fill rate etc. From A to C the data is completely different, but the shape is exactly the same.
Running Analyze for table A which shape-shifts into B over time (which is highly unlikely) will certainly see the values become
"stale". Running Analyze for A which eventually changes into C (very likely) will have minimal effect on the outcome of the Analyze
data and hence on the outcome of the Query Planner decisions.
To re-iterate the question posed earlier - If you can show that the QP makes a worse decision after analyzing a table - OR - that
the decision degrades over time for a table of which the shape does not change effectively - I am sure the case would be considered
as undesirable and the devs would probably be interested in doing something about it.
Other options include forcing the use of an Index or Indices (see "USING" in
the SQLite SQL docs) or using cross-joins and the like.
A this point we are
considering writing VDBE code directly and bypassing the
parser. Has anyone else done this? Is it going
to be a huge ugly
can-of-worms if we do
that?
Not just a can of worms - an insane one, and what is worse, you will immediately lose any benefit of periodic updates and all the
other advances in technology so frequently visited upon SQLite because your version is no longer interchangeable with the new ones.
Further to that, this speaks of the willingness to take 3 steps in the wrong direction to avoid one step in the right direction -
maybe some past experience influence or some other unknowns which are not immediately obvious now is influencing this thought? (I
only ask because this is not uncommon).
If you do have a table of which the shape keeps changing to the point it needs re-analyzing every few minutes, then you are working
with the worst DB in history and should really make your own native storage solution - but I am 90% certain this is not the case and
you are simply overlooking something or not doing something efficiently - which we will be glad to help with if you post schemata
and maybe link an example DB or such.
Unlike me, some other people on here are extremely knowledgeable on SQL and specifically the inner workings of SQLite - Tell us the
full story, we might save you hundreds of hours.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
