Re: [sqlite] SQLITE_ENABLE_STAT3/4 causing expensive statement recompilation

2013-12-12 Thread Dominique Devienne 
On Thu, Dec 12, 2013 at 4:19 PM, Richard Hipp  wrote:

> On Wed, Dec 11, 2013 at 3:00 PM, Nick Hutchinson  >wrote:
>
> >  I ran my test app under Visual Studio's profiler, and saw that a
> > substantial amount of time is spent in calls to sqlite3Reprepare().
> Reading
> > the docs at http://www.sqlite.org/c3ref/prepare.html, I assume SQLlite
> is
> > recompiling the statement because it thinks it can find a better query
> plan
> > based on the parameters I'm binding to it.
>
> Correct.  With SQLITE_ENABLE_STAT[34], whenever you change parameter
> bindings, SQLite is forced to reprepare the statement.  This is because the
> choice of algorithm depends upon the actual values bound to parameters.  In
> your case, the changes to the bound parameters does not make a difference
> in the algorithm, but SQLite has no way of knowing that without going
> through the whole re-prepare process.
>
> Yes, this might require more time preparing statements.  That's one of the
> trade-offs for using SQLITE_ENABLE_STAT[34].  And, that is also why
> STAT[34] is disabled by default.
>

Perhaps it needs to be finer-grained and enabled/disabled on a
per-statement-basis as well?

Or perhaps on a per-table basis instead? Just thinking aloud. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_ENABLE_STAT3/4 causing expensive statement recompilation

2013-12-12 Thread Richard Hipp 
On Wed, Dec 11, 2013 at 3:00 PM, Nick Hutchinson wrote:

>  I ran my test app under Visual Studio's profiler, and saw that a
> substantial amount of time is spent in calls to sqlite3Reprepare(). Reading
> the docs at http://www.sqlite.org/c3ref/prepare.html, I assume SQLlite is
> recompiling the statement because it thinks it can find a better query plan
> based on the parameters I'm binding to it.
>


Correct.  With SQLITE_ENABLE_STAT[34], whenever you change parameter
bindings, SQLite is forced to reprepare the statement.  This is because the
choice of algorithm depends upon the actual values bound to parameters.  In
your case, the changes to the bound parameters does not make a difference
in the algorithm, but SQLite has no way of knowing that without going
through the whole re-prepare process.

Yes, this might require more time preparing statements.  That's one of the
trade-offs for using SQLITE_ENABLE_STAT[34].  And, that is also why
STAT[34] is disabled by default.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_ENABLE_STAT3/4 causing expensive statement recompilation

2013-12-11 Thread Nick Hutchinson 
Hi,

I'm finding that if I compile with either SQLITE_ENABLE_STAT3 or
SQLITE_ENABLE_STAT4, repeatedly executing a prepared SELECT statement is
substantially slower.

I have a silly benchmark app that populates a database with test data,
creates some pertinent indexes, runs ANALYZE, then executes a particular
SELECT statement against the database several thousand times. Executing all
these SELECTs normally takes a few seconds, but it's 10x slower when SQLite
is compiled with SQLITE_ENABLE_STAT4.

Curious, I ran my test app under Visual Studio's profiler, and saw that a
substantial amount of time is spent in calls to sqlite3Reprepare(). Reading
the docs at http://www.sqlite.org/c3ref/prepare.html, I assume SQLlite is
recompiling the statement because it thinks it can find a better query plan
based on the parameters I'm binding to it. Unfortunately, the time needed
for this recompilation is dramatically outweighing any potential savings
that a better query plan could offer!

Has anyone else had a similarly negative experience with
SQLITE_ENABLE_STAT3/4?

Cheers,
Nick
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users