I have seen a prepare take over a second.
Take for example this recursive Sudoku SQL:
WITH RECURSIVE
input(sud) AS (
VALUES('.3..7....6..1.5....98....6.8...6...34..8.3..17...2...6.6....28....4.9..5....8..7.')
),
digits(z, lp) AS (
VALUES('1', 1)
UNION ALL SELECT
CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
),
x(s, ind) AS (
SELECT sud, instr(sud, '.') FROM input
UNION ALL
SELECT
substr(s, 1, ind-1) || z || substr(s, ind+1),
instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
FROM x, digits AS z
WHERE ind>0
AND NOT EXISTS (
SELECT 1
FROM digits AS lp
WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)
OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1)
OR z.z = substr(s, (((ind-1)/3) % 3) * 3
+ ((ind-1)/27) * 27 + lp
+ ((lp-1) / 3) * 6, 1)
)
)
SELECT s as solution FROM x WHERE ind=0
RBS
On Thu, Feb 9, 2017 at 9:45 AM, R Smith <[email protected]> wrote:
>
>
> 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
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
