On 2017/02/09 2:55 AM, James K. Lowden wrote:
some complicated statements can take minutes to simply prepare on a
large DB
I doubt that. I've never seen it, and I see no reason it should be
true. A huge SQL query might be 1000 tokens. Why should preparing it
take 1 second, let alone minutes?
--jkl
I would doubt it too - I know it doesn't feel sensible, and I was quite
surprised when I experienced it the first time, but after I realised
what was going on, it made sense (at least, I hope I have the right
idea, this is purely me guessing at the inner workings of SQLite, but
the fact remains: Preparing some statements can take a lot of time).
ISTM that it's a case of SQLite having to run through the set-up of a
statement before embarking on the row production (via STEP, RESET etc.)
Consider a complicated SELECT statement with CTEs galore that group up
items and then hold the aggregates and the like, then the final SELECT
selects from those CTEs. The sqlite3_prepare() [and its ilk] has to
compute all the CTEs to know the answers before being able to pop out
the first line of the final SELECT in the first sqlite3_step() that
follows. My initial thoughts was that the prepare just wrote the
internal program, and only when the step() is called does any processing
take place - but from my tests it seems the sqlite3_prepare() itself
does a whole lot of processing, and it made sense to me afterward. I
believe even the simple case of LIMIT x, y causes the prepare to iterate
over the first x lines before handing control back to the app awaiting
the step(). Another beast prepare() was doing "SELECT COUNT(a) FROM t;"
where a was not indexed and t was huge.
I could however be wrong, my testing was limited to my own curiosity and
testing some premises for the SQLite tools I made, and it's a bit long
ago, but I seem to recall it this way.
Maybe someone with real knowledge on this could weigh in.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
